Transaction log backup problems

An interesting situation today.  During an audit of our servers for last backup date/times, I noticed one particular database hadn't been backed up.  I set out to remedy the situation and kicked off the backup.

Two minutes later, I get the following alert (Severity 17) from SQL Server:



O...kay, then.  So I check the maximum size of the database file in question.  This is set to 333MB, 1MB filegrowth.  The act of attempting to backup the database filled the transaction log, and as this database was set to the full recovery model (with no transaction log backups) SQL Server couldn't reuse the log.  This manifests itself in the log_reuse_wait_desc column in sys.databases, and causes a Severity 17 alert to be issued.

So - to shrink the database file first, so I can do something about it.  Problem - there's 0% free.  It's completely full.  When I try to shrink it anyway, I get a warning that the transaction log is full.

Next option - ALTER DATABASE x SET RECOVERY SIMPLE.  This sets the recovery model from full to simple and allows log reuse.  In this way, I can now modify the file max size, and the problem is resolved.  Checking the 'size' column in sys.database_files for all databases on this server (don't forget to cast to float and divide by 128 for MB), I note three others suffering from the same size cap.  As the logs aren't full yet, I can just modify the size, and make a note to return to it soon to fix the recovery models and backups the way I want them.

For more information on the LOG_BACKUP wait type, read Paul Randal's comprehensive explanation here -at http://sqlity.net/en/1830/log-reuse-waits-explained-log_backup/


Comments

Popular Posts