Issues with SQL Server backup log with no_log or truncate_only

Problem

On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?

Solution

When managing transaction log growth, the first thought that may come to mind is to use the NO_LOG or the TRUNCATE_ONLY commands. These commands allow you to reset the data that is stored in the transaction logs, so that you are able to reuse the space without having to continue to grow the transaction log or having to do a log backup. In some cases this may be what you want to do, but this should not be done as a general practice.

Let’s take a look at an example of what happens and how this could impact your backup processing.

First we created a database called “Test” and then executed these statements.

-- create a table
create table dbo.test (id int identity (1,1), description varchar(50)) -- insert a record
insert into dbo.test (description) values ('test')
go
-- do a full backup
backup database test to disk='C:\test.bak' with init 
-- insert another record
insert into dbo.test (description) values ('test')
go
-- do a log backup with no_log (or truncate_only)
backup log test with no_log 
-- insert another record
insert into dbo.test (description) values ('test')
go
-- do a log backup
backup log test to disk = 'C:\test_1.trn'

SQL Server 2000 Behavior

When these statements are run on SQL Server 2000 the process works, but when we run the last command we get this error message, but the backup still runs and the file is created.

SQL Server 2005 Behavior

When this is run on SQL Server 2005 the process does not complete and we get this different error message. The message is a little misleading because it says there is no current database backup, but we already ran the full backup without issue. The real issue is that we truncated the transaction log prior to this backup and broke the log chain and that is what is causing this error.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Restore Process

So for SQL Server 2005 we know that we did not create a log backup, so there is no log backup to restore, but what happens if we try to restore the full backup and transaction log backup from SQL Server 2000. Below are the commands to do the restore.

-- restore the full backup using the norecovery option
restore database test from disk = 'C:\test.bak' with norecovery -- restore the log backup 
restore database test from disk = 'C:\test_1.trn'

The restore of the full backup works fine, but when we try to restore the transaction log backup we get this error message:

Server: Msg 4330, Level 16, State 4, Line 1
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

So what does this mean?

It means a few things, first using no_log or truncate_only invalidates any transaction log backup that is performed after this statement is run. Therefore if you have a process in your nightly routine where you are truncating the transaction log to free up space and shrink the log any transaction log backups that you run after that will not do you any good. So by trying to not let your transaction log size get out of control you have actually caused worst issues for yourself.

Another thing this means is that Microsoft has gotten smarter with SQL Server 2005 and does not allow you to perform additional transaction log backups after you issue a no_log or truncate_only. In SQL Server 2005 books online it says the following:

We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately. In addition: This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

There is a time and place where this comes in handy, if you have a huge transaction log and you do not want to do a backup, this option allows you to free up the space and then shrink the transaction log, but it was not intended to use as a general practice to be used every day.

For SQL Server 2005 and 2008, Microsoft recommends changing the recovery model to SIMPLE instead of using no_log or truncate_only.

Removed from SQL 2008

In SQL Server 2008 this option no longer exists.

Next Steps

• If you are using the no_log or truncate_only option take a look at your recovery path and what your potential exposure may be
• Instead of just issuing a backup log with no_log you should either put your database in the SIMPLE recovery mode or issue transaction log backups on a regular basis