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

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

PingBack from http://www.pythian.com/blogs/1413/log-buffer-126-a-carnival-of-the-vanities-for-dbas

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

SELECT *

FROM sys.database_recovery_status

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

Invalid object name 'sys.database_recovery_status'.

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Sharon

http://www.autoloans101.info

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!

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

PingBack from http://delpak.ir/1387/10/31-%d9%85%d9%82%d8%a7%d9%84%d9%87-%d8%a8%d8%b1%d8%aa%d8%b1-%d8%b3%d8%a7%d9%8a%d8%aa-sqlblog-%d8%af%d8%b1-%d8%b3%d8%a7%d9%84-2008/

Partition recovery requires file system reconstruction just like standard file recovery software does. The best part is if a partition program works you don't have to reinstall everything. Drive failure needs a service company.

Take a moment to look beyond cost in data recovery service..Taking a risky action that could cause everything to be a disaster is not an option either. If your goal really is to have the data back intact do your homework. I say it this way because with

With data recovery software you must respond quickly in most cases to get the greatest results. Otherwise if you continue to use a computer while wanting to recover something you're chances greatly diminish. I go into the possible solutions and how to

Not many people even think of disk recovery until the time comes to perform it. In fact few persons rarely save their files properly either. That's ok most of time because we have disk recovery software. It will get us back up and running in no time as

File recovery tools mainly address lost files. Some may also put an undelete function in for free since it's so common. In either case you can't find your file or can't get it back. The solutions are many and confusing because they all are marketed alike.

It's obvious but all Windows and MAC operating systems will have undelete and search recovery utilities built into them. Try this before you go off spending a lot of money on data recovery products and services.

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