asked
Truncate the Log
I plan to truncate the logs to SQL server database. What are the steps I have to do since it is production. I have seen different replies: some ask to backup the tlogs some do not . What command to use: DBCC ?? truncate_only
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
What version of SQL Server are you running? The ability to manually truncate the log file was removed around SQL Server 2008 (I think). Also, if I recall correctly, backing up the transaction log through a normal backup process should truncate the log along with the checkpoint process. Note: truncation is a logical process, it won't reclaim physical space on the drive. This requires a shrink. Source MSDN:
https://technet.microsoft.com/en-us/library/ms186865(v=sql.105).aspx *"The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued"* (from above source) What issue are you attempting to solve? Hope this helps!
I am trying to deal with the space issue the logs are taking. The version is SQL Server 2008.
You will want to look at shrinking the log, not truncating it. I would caution that shrinking the log file (especially if it needs the space) can be expensive as the log might have to grow back out and then you potentially get into a vicious cycle. Grow, shrink, grow, shrink, etc.
will backup of the log first break the existing log backup chain?
The question you have to ask yourself is why are the logs large? Are they in FULL recovery and you are not taking log backups? If so and you don't need point in time recovery you can switch to SIMPLE recovery model and shrink the log file (DBCC SHRINKFILE). If they are in FULL recovery and you need point in time recovery however the logs are massive and you want to basically start fresh you can switch to SIMPLE, shrink the file, switch back to FULL, expand the log to the size you think it should be, perform an FULL backup and then schedule regular log backups. If the databases are in FULL and you are performing regular log backups, then it is very likely that the logs are the size they need to be based on the current workload and index maintenance you have going on. The only way you could potentially reduce the size is by increasing the interval of the log backups.
17 People are following this question.
