question

vivekchandra09 avatar image
vivekchandra09 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
loglog-backupsql-server-logtruncate
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
JohnM avatar image
JohnM answered
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!
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But you'll need to backup the log first.
1 Like 1 ·
I am trying to deal with the space issue the logs are taking. The version is SQL Server 2008.
0 Likes 0 ·
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.
0 Likes 0 ·
Ok, I think that I was slightly confused here, my fault. I think you are looking for DBCC SHRINKFILE (FileID, TRUNCATEONLY); Make sure you have the right file ID. You can find the file id from sys.database_files.
0 Likes 0 ·
will backup of the log first break the existing log backup chain?
0 Likes 0 ·
No, it should not break the existing log backup chain. The backup will help to mark the VLF's as inactive which will help you to truncate the log file.
0 Likes 0 ·
Show more comments
Tim avatar image
Tim answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.