question

eghetto avatar image
eghetto asked

Why can I shrink my Log only if I previously perform a Log-Backup TWICE?

Dear folks, I have a database in FULL recovery mode. Due to an exceptional bulk data load my log-file grew from 25GB to 50GB. I would like to shrink it back to 25GB again, because I need the space for other dbs as well. I wonder why this procedure does **NOT** work: Perform Log-Backup -> Execute "DBCC SHRINKFILE (N'Test1_log' , 25000);" But this **DOES** work: Perform Log-Backup -> Perform Log-Backup (again) -> Execute "DBCC SHRINKFILE (N'Test1_log' , 25000);" Is there a brief explanation for this? Thanks!
sql-server-2005backupshrink-log-file
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.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Refer to BOL : In the section about `log_reuse_wait` column, it states > If the reason is LOG_BACKUP, it may take two backups to actually free the space. Also see [Factors that can delay log truncation][1] [1]: http://technet.microsoft.com/en-us/library/ms345414.aspx
3 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.

that's an odd quirk
0 Likes 0 ·
yeah, a sql server engineer might be able to explain, what happens internally... anyone out there? :)
0 Likes 0 ·
Sometimes shrinking is not possible until log truncation has happened. (there is no free space to shrink away). It's entirely feasible due to the way that the log file is managed, but without knowing the value of `log_reuse_wait` prior to the initial backup, it will be difficult to state in your case.
0 Likes 0 ·

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.