THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: When is FULL Recovery not Really FULL Recovery

You may think that having a database set to FULL Recovery Model means that your database can be fully recovered. The key word in that previous sentence is 'can'. It is possible to fully recovery a database in FULL recovery, or to restore it to an arbitrary point in time, or up to the point of a system failure, IF you have been a good DBA and you have been taking your regular backups.

FULL Recovery means that all your changes to the database are fully logged, and you might think that also means that the log records stay in the log until the log records are safely stored away in a log backup.

But that is not completely correct. You database can be in FULL Recovery model, but also in a state called auto-truncate mode.

Most people are familiar with auto-truncation in SIMPLE Recovery, and of course that is one way to get a database into auto-truncate mode.

If your database is in auto-truncate mode, the log will be truncated every time a CHECKPOINT is run against the database. Checkpoints happen at very frequent intervals, which by default is usually about every minute.  Truncation means that all log records in inactive parts of the log are marked as available to be overwritten if the log space is needed.  Truncation does not physically shrink the log file, but it can keep it from physically growing.

So how do you get in auto-truncate mode? There are three ways:

1. As a mentioned above, if you put your database into SIMPLE Recovery, it will be go into auto-truncate mode.

2. If you backup the log without saving the log records, using BACKUP LOG ... WITH TRUNCATE_ONLY, no more log backups can be made, and the database is now in auto-truncate mode.

3. If you have never taken a FULL backup of your database, you can never take log backups, so there is no point in keep log records available. Your database is in auto-truncate mode until the first FULL backup of the database is made. 

     3a. If you have never taken a FULL backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery. (Thanks to Hugo)

You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log.

So how can you tell if your database is in auto-truncate mode? The recovery_model_desc column in sys.databases only tells you what recovery model you have set, not what behavior your database is using. To see whether a database is in auto-truncate mode, you can query a undocumented system view called sys.database_recovery_status and look at a column called last_log_backup_lsn. If that value is NULL, it means the database is not maintaining a sequence of log backups and it is in auto-truncate mode:

SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status

Here's a whole script you can use to test this for yourself:

-- Check for auto-truncate mode
-- If last_log_backup_lsn is NULL, log is in auto-truncate mode

CREATE DATABASE FOO
GO
-- Check status after first creating db
SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP DATABASE foo TO disk = 'C:\foo.bak'
GO
-- Check status after backing up db
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
BACKUP LOG foo WITH TRUNCATE_ONLY
GO
-- Check status after truncating log
SELECT db_name(database_id) AS 'database', last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = db_id('foo')
GO
DROP DATABASE foo
GO

Note that there is a change in behavior in SQL Server 2008. The BACKUP LOG ... WITH TRUNCATE_ONLY option is no longer available. If you want to force the log to be truncated (and enter auto-truncate mode) the recommended method is to change your recovery model to SIMPLE.

Have fun!

~Kalen

Published Sunday, November 30, 2008 4:30 PM by Kalen Delaney
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hugo Kornelis said:

Hi Kalen,

Good post. But allow me to pick a nit. The third way to get into auto-truncate is not "you have never taken a full backup", but "you have never taken a full backup SINCE THE LAST TIME YOU SWITCHED FROM SIMPLE TO FULL RECOVERY".

Probably obvious to any reader, but I wanted to point it out anyway.

Best, Hugo

December 1, 2008 4:08 AM
 

Kalen Delaney said:

Hi Hugo

I've made the correction, and also enhanced on your correction a bit!

~Kalen

December 1, 2008 11:40 AM
 

James Luetkehoelter said:

Great Kalen, excellent point - I've seen it more than once. Actually it is true if you've never taken a full backup, you're dead in the water anyway Hugo :)

December 1, 2008 7:55 PM
 

David said:

There's a typo in the paragraph "So how can you tell...."  "sys. recovery_recovery_status" should be "sys.database_recovery_status"

December 6, 2008 11:42 PM
 

Kalen Delaney said:

Thanks David... I've done that several times recently.. the code is correct (because I test that before publishing) but the description is a bit sloppy..

It should be fixed now ...

~Kalen

December 6, 2008 11:54 PM
 

Rob Maurer said:

SELECT *

FROM sys.database_recovery_status

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.database_recovery_status'.

December 12, 2008 9:17 AM
 

Mike Suarez said:

Thanks for the explanation!

This actually clears a lot up for me. A while back, I was playing around with transaction logs in different recovery modes. I added data to tables, invoked checkpoints, and shrunk the files, all while looking at the physical size of the files every step of the way. I was suprised to see the same behavior under both simple and full recovery modes. I just assumed that maybe i didnt understand what was going on as well as i thought i did. But now I know that the reason they were doing the same thing was because they were still both in auto-truncate mode (because of reason #3). Now I am back to believing that I understand it. Thanks!

December 22, 2008 5:25 PM
 

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:38 AM
 

martinz said:

Hi Kalen,

Thanks for providing these details.

I believe that there may be another senario to get into auto-truncate.

That is when the database is reverted from a snapshot.

I've been playing with the script you provided and have added a few details below.

CREATE DATABASE FOO

GO

-- Check status after first creating db

SELECT db_name(database_id) as 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

BACKUP DATABASE foo TO disk = 'C:\foo.bak'

GO

-- Check status after backing up db

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

-- Check status after truncating log

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

ALTER DATABASE [FOO] SET RECOVERY SIMPLE WITH NO_WAIT

GO

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

USE [master]

GO

ALTER DATABASE [FOO] SET RECOVERY FULL WITH NO_WAIT

GO

BACKUP DATABASE foo TO disk = 'C:\foo.bak'

GO

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

CREATE DATABASE foo_dbss ON

( NAME = foo, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\foo_snapshot.ss' )

AS SNAPSHOT OF foo;

GO

RESTORE DATABASE foo from

DATABASE_SNAPSHOT = 'foo_dbss';

GO

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

GO

DROP DATABASE [foo_dbss]

GO

DROP DATABASE foo

GO

November 23, 2009 4:50 AM
 

SteveLaRochelle said:

I have a database that appears to be in auto-truncate mode, yet the truncation never happens.  DB is in Simple mode; last_log_backup_lsn in database_recovery_status is NULL.  Full backups are taken nightly.  However the % of transaction log continues to grow, leading to transaction log growth, hundreds of VLFs all Status=2(Active), etc. The database has one snapshot publication which seems to be the cause; existence of it prevents the auto-truncation.  Drop/recreate of publication does clear the active portion of the log.  Same with (manually) running sp_repldone NULL,NULL,0,0,1.  Though both of those seem like band aids to cover or compensate for whatever the real problem is.

February 26, 2010 11:29 AM
 

Kalen Delaney said:

Hi Steve

If your VLFs are all active, it doesn't matter how many truncates you do. Truncate (whether by being in SIMPLE mode or otherwise) will not affect active log. (Note that status 2 doesn't necessarily mean active, it just means not truncatable, as you are finding. Also, backing up the full database never has any effect on the log.)

This sure sounds like replication is the culprit. Have you tried completely removing replication?

http://support.microsoft.com/kb/324401

~Kalen

March 1, 2010 6:43 PM
 

SteveLaRochelle said:

I just ran some tests, and replication looks to be the problem; however, replication TO this database may be the cause?  I removed all replication to/from this DB.  Transaction log usage went to a minimal amount.  I recreated my lone snapshot publication then executed a few thousand dummy transactions.  I saw the log file usage, and file, grow as the transactions were occurring.  But a minute later, usage is back to a minimal amount which is expected in a DB that is in auto-truncate mode.  I haven't recreated the transactional replication that is going TO this database yet, but is it possible that being a push subscriber is what is preventing my log file from auto-truncating?  If so, how, and how to fix?  I have plenty of other auto-truncate DBs that are push subscribers that don't have this problem. Seems that the combination of push subscriber and IsPublished=1 leads to my issue.

March 2, 2010 12:17 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement