question

sqlqa avatar image
sqlqa asked

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

Hi We are using SQL 2005, windows server 2003. We have 24 db in our SQL Server. We are schedule backup jobs mon to fri. Transaction log backup every one hour and schedule tue to sat( Not schedule sun & mon bcos error comes bcos there is no current db backup) My problem is 1)one db has throws continuously LOG_BACKUP on log_reuse_wait_desc of sys.databases so how to solve this? 2) During Sun and Mon Log files are growing very large due to transaction log backp not available. **How to reduce log file size during Sun and Mon?**
transaction-loglog-backup
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.

Kev Riley avatar image
Kev Riley answered
OK there's a few things to discuss here. `log_reuse_wait_desc` can still report LOG_BACKUP for a number of reasons: - is the database used in a log-shipping scenario? - a subsequent log backup is needed I suggest you review these: http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/ http://technet.microsoft.com/en-us/library/ms345414(v=sql.90).aspx I'm not sure why you would get an error on Sun and Mon when you try and run a transaction log backup - but why not try and run your backup strategy 7 days a week rather than just 5 - corruption can occur any day!
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.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
1) Log_reuse_wait can be caused by several reasons, I suggest to study this article: http://msdn.microsoft.com/en-us/library/ms345414.aspx 2) If you have a large transaction, this behaviour is normal. Do you run a database maintenance task like reindex? Or is an application doing some maintenance on his data? General tip: have you checked the number of virtual logfiles? That might also be a reason for bad logfile performance. Check this video: http://blogs.msdn.com/b/sqlserverfaq/archive/2011/06/01/what-are-virtual-log-files-vlf-in-sql-server-video.aspx?Redirected=true
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.