NOTE:  This is an expansion of a note that was posted on our company news page over a year ago, but I’ve recently encountered the same issue a couple of times within a week, so thought it was worth repeating.

Occasionally I’ve come across a site where backups are taken to disk = ‘NUL’. Note that’s NUL with 1 L and not NULL with 2 L’s. This allows a database in full recoverability to perform a “pretend” log backup and therefore the log file to be cleared and re-used. No resultant file is placed on disk so I’ve seen it recommended in a few places online as a quick fix where a log file has grown out of control.

The important thing to know about this is that you have just broken your recoverability chain and have no point in time recoverability until a new full backup is taken(Or if you want to be tricksey a differential backup will do the trick.. Therefore it should NEVER be part of a regular maintenance plan (especially in conjunction with a scheduled shrink….EK!).

If point in time recoverability is not important to you – use SIMPLE recoverability mode and your transaction log will be managed by SQL Server itself. If you do require point in time recoverability then schedule regular log backups and if your transaction log is still growing larger than expected then look at the activity that is causing it rather than resorting to a sledgehammer fix like using backup to disk = ‘NUL’. If you use that you have achieved nothing more or less than taking a regular log backup and then deleting the file.

Now that we’ve talked about it does, it’s worth noting that this may automatically start appearing in your backup reports without you having changed anything on the SQL Server.  You can get a list of backups that are taken on your server by using this script, and I suggest you do that, and check for any extra backups that occur.  If you start to see a bunch of log backups to the NUL device appear from nowhere, go talk to your system administrator about what new backup software they are using, and once they’ve admitted they have just put a new and shiny backup product in place you have my full permission to bash them upside the head and tell them not to change stuff on your server without talking to you.  There’s a couple of popular backup product which ‘helps’ people with their SQL backups by offering to ‘prune’ or ‘truncate’ SQL Logs.  Make sure you read what you mean before implementing this as you may be stung by the NUL backup issue.

The most annoying bit about this is it is a tickbox or checkbox which doesn’t give you information on what it will actually be doing.  System administrators never want extra logs filling up their disk space so a checkbox offering to ‘prune’ them sounds like a great idea.  Of course, a database level setting that automatically shrinks your database to stop it filling up all that disk space sounds great to a system administrator too(HINT:  It’s not).