Geek City: When is FULL Recovery not Really FULL Recovery

re: Geek City: When is FULL Recovery not Really FULL Recovery

Hugo Kornelis — Mon, 01 Dec 2008 09:08:00 GMT

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

re: Geek City: When is FULL Recovery not Really FULL Recovery

Kalen Delaney — Mon, 01 Dec 2008 16:40:15 GMT

Hi Hugo

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

~Kalen

re: Geek City: When is FULL Recovery not Really FULL Recovery

James Luetkehoelter — Tue, 02 Dec 2008 00:55:31 GMT

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 :)

re: Geek City: When is FULL Recovery not Really FULL Recovery

David — Sun, 07 Dec 2008 04:42:08 GMT

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

re: Geek City: When is FULL Recovery not Really FULL Recovery

Kalen Delaney — Sun, 07 Dec 2008 04:54:21 GMT

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

re: Geek City: When is FULL Recovery not Really FULL Recovery

Rob Maurer — Fri, 12 Dec 2008 14:17:26 GMT

SELECT *

FROM sys.database_recovery_status

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

Invalid object name 'sys.database_recovery_status'.

re: Geek City: When is FULL Recovery not Really FULL Recovery

Mike Suarez — Mon, 22 Dec 2008 22:25:42 GMT

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!

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:38:04 GMT

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

re: Geek City: When is FULL Recovery not Really FULL Recovery

martinz — Mon, 23 Nov 2009 09:50:41 GMT

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

-- 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')

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

-- 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')

-- 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')

ALTER DATABASE [FOO] SET RECOVERY SIMPLE WITH NO_WAIT

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

USE [master]

ALTER DATABASE [FOO] SET RECOVERY FULL WITH NO_WAIT

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

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

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;

RESTORE DATABASE foo from

DATABASE_SNAPSHOT = 'foo_dbss';

SELECT db_name(database_id) AS 'database', last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id('foo')

DROP DATABASE [foo_dbss]

DROP DATABASE foo

re: Geek City: When is FULL Recovery not Really FULL Recovery

SteveLaRochelle — Fri, 26 Feb 2010 16:29:06 GMT

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.

re: Geek City: When is FULL Recovery not Really FULL Recovery

Kalen Delaney — Mon, 01 Mar 2010 23:43:45 GMT

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

re: Geek City: When is FULL Recovery not Really FULL Recovery

SteveLaRochelle — Tue, 02 Mar 2010 17:17:13 GMT

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.