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

Question

question

eghetto asked Mar 23, '12

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-2005 backup shrink-log-file

10 |1200

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

Answer 1 · 2012-03-23T11:51:03Z

Kev Riley answered Mar 23, '12

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

10 |1200

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

KenJ commented · Mar 23, 2012 at 01:55 PM

that's an odd quirk

0 ·

eghetto commented · Mar 23, 2012 at 03:25 PM

yeah, a sql server engineer might be able to explain, what happens internally... anyone out there? :)

0 ·

Kev Riley ♦♦ commented · Mar 23, 2012 at 04:32 PM

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 ·

question