Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Issues with SQL Server backup log with no_log or truncate_only

MSSQLTips author Greg Robidoux By:   |   Read Comments (3)   |   Related Tips: More > Backup

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.

There is no current database backup. This log backup cannot be used to roll forward a preceding database backup.
Processed 1 pages for database 'test', file 'test_Log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.012 seconds (0.426 MB/sec).

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.  Take a look at this list of depreciated features.

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


Last Update: 3/27/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 27, 2008 - 12:24:23 PM - mikeb Read The Tip

There are some run-away log situations in SQL 2000/2005 where there is no other way to reduce the log file size unless you truncate it and do a shrinkfile on it. You can try many things like backing up the log, shrinking the log, adding dummy transactions like suggested by some kb articles but the log will never become smaller. In such cases, there is no harm in using this command to truncate the log (to be safe do a log backup before doing the truncation), do a shrinkfile on the log file and immediately do a FULL database backup.

Switching to simple recovery mode is not an alternative since you can't anyway do transaction log backups in simple recovery mode. The best option when you run into a run-away log situation is to do the backup with no_log, shrink the log file and then immediately backup the database.

 


Thursday, April 10, 2008 - 6:31:43 AM - boltbrkr Read The Tip

There is nothing wrong with using truncate only and no_log in your nightly scripts as long as they are followed immediately with a full backup. Once again Microsoft thinks everybody lives in a perfect world. In the future when this useful statement is taken away from us, what will we do to regain valuable log space?


Friday, April 11, 2008 - 10:25:12 AM - grobido Read The Tip

The functionality won't totally disappear it is just that you will need to change the recovery model for this to clear the transaction log.

 I do agree there that this is needed, but as long as you use this and take another full backup right afterwards you should be covered.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.