Find Out What Your Log Is Waiting For

Another tidbit from the PASS Summit that I am only now getting around to blogging about…

Did you know that your transaction log file is circular in nature? That is, when your log has reached the end of the physical file it will go back to the very beginning and overwrite any virtual log file (VLF) segments that have been ‘cleared’? Well, it will, and it does. That is how log files work, like a cat chasing her tail in the middle of the kitchen while you try to bake some bacon for dinner.

Now, if your log file is properly sized, and you are able to ensure checkpoints occur frequently enough, then the end of the logical log file (or the active log) will never reach the beginning of the logical log file. If it does, then you will either get an error message that your log file is full, your log file will grow (if you have autgrowth enabled), or you trip over your cat.

Now for the tidbit. The catalog view, sys.databases, includes the column log_reuse_wait_desc. This column describes what your transaction log is waiting for in order to reuse VLF space. That means this statement:

SELECT name, log_reuse_wait_desc FROM sys.databases

can be used in conjunction with your database backups. How? Well, if you were to monitor your databases, say with Operations Manager, you could find a database with a log file waiting for LOG BACKUP. Armed with this information, you could issue a transaction log backup immediately. (NOTE: just because you can, doesn’t mean you should.)

But in theory, you would be able to key off of this column in order to build yourself and even smarter backup process than what you have currently. Imagine if an end user inadvertently kicked off an ad-hoc process that was causing a lot of logged activity. Despite spending days and weeks to ensure that everything was properly sized, your log file is poised to grow in size, fragment itself, and cause some performance degredation. You could avoid that by having a check in place on the log_reuse_wait_desc column, issue an ad-hoc transaction log backup, and clear those VLF’s to be reused as needed.

I can also think of another good use of this column, but will leave that as an exercise for the reader.