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?**
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!
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
15 People are following this question.
