So I can’t use BACKUP LOG WITH TRUNCATE ONLY – but what can I do instead?

In an earlier blog entry, I talked about "Deprecated" code and I highlighted the fact that the BACKUP LOG WITH NO_LOG and BACKUP LOG WITH TRUNCATE_ONLY statements are no longer allowed in SQL Server 2008. I mentioned that these functionally equivalent statements were a quick way to truncate the log and free up space. The downside of the statements was that they would break the "chain" of log backups exposing the database to potential data loss until a full database backup could be made.

OK, so Microsoft is trying to protect us from ourselves, but what should we do instead? Well, the official "Deprecated Database Engine Features in SQL Server 2005" document first of all, refers to a replacement of "None". Fine, so what do we do now if there is no replacement? The document continues: "The transaction log is automatically truncated when the database is using the simple recovery model". This is true, but that means we can only restore the database back to the time of the last full or differential database backup. No recovery up to the point of failure as with the Full recovery model. You see, we cannot use the transaction log for recovery with the Simple recovery model. Again, the document continues: "If you need to remove the log backup chain from a database, switch to the simple recovery model". (The implication is that we would then switch back to Full recovery model as soon as possible).

Again, this is true but this also breaks the "chain" of log backups and therefore exposes the database until a full database backup can be made. So we are back to square one. Microsoft has given us a great work-around to get back to the bad practices that we could do before. So what do we do? Well, truthfully, the main reason we used the NO_LOG or TRUNCATE_ONLY options was that we had run out of disk space. These were the days when disk space was somewhat scarce and expensive. The real solution to the problem here is to find some disk space somewhere, even temporary disk space, and backup the log to there. (Tape backups are fine, but take longer). Then continue as normal because a normal BACKUP LOG statement will both backup and truncate the log at the same time. Note that truncation will free up space within the transaction log for new transactions but will not reduce the size of the log file itself. If the transaction log is growing too large then you should increase the frequency of log backups. (The Full database backup does not actually truncate the transaction log at all).

If we need to recover the free space within the transaction log, we would also need to perform a SHRINKFILE operation. (believe it or not, because of the internal storage of the transaction log, sometimes we have to run this sequence twice to free up the maximum space. Just try it and see!). Therefore you are preserving the chain of log backups and have freed up space too. If the disk space you found is truly temporary, you should perform a Full backup immediately. Then continue your normal cycle of backups. Using this method you have, at no time, exposed your database to potential data loss. Of course, all backup and recovery processes need to be thoroughly tested and documented, along with regularly scheduled fire-drills.

This way, when a disaster occurs we should be able to follow a simple checklist and run some scripts. No panic. No need to update our resumes. We need to plan for the worst and hope for the best. That's the key.

Good luck!


Recent blog posts...

Group Policy for SQL Server 2008 – the Declarative Management Framework (DMF)

The Boston Marathon uses SQL Server - I wish I was quick enough to stress it!

So you want to store movies in a SQL Server database? Go ahead, make my day.

Has Microsoft Deprecated your SQL code in 2008?”

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2008 IDG Communications, Inc.