Boot Page Flag for Cleanly Shut Down SQL Server Database

database-internalssql serversql-server-2012

I have been watching the SQL Server: Advanced Corruption Recovery Techniques by Paul Randal on Pluralsights. During module 4, he discusses the fact that if you have a missing log file it is important to know if the database was shutdown cleanly or not. This would determine if SQL Server will run crash recovery or not.

He goes on to mention, that the flag for this can be found in the database boot page (1:9 is my assumption based on the course materials and backed up through this link on microsoft). However, I do not see any obvious flags inside the page using –

DBCC TRACEON (3604);
DBCC PAGE ('stack',1, 9, 2) WITH TABLERESULTS;
DBCC TRACEOFF (3604);

In looking for this online, I am not able to find the information about the Boot page (1:9). I was able to find that sys.databases has a column for is_cleanly_shutdown which stands for:

1 = Database shut down cleanly; no recovery required on startup

0 = Database did not shut down cleanly; recovery is required on startup

However, I would like to follow along with the class, can someone advise what I am looking for in the boot page? Also, how to get the database to show it was cleanly shutdown or the proper steps to do this?

I am running SQL Server 2012.

Best Answer

It's part of the dbi_status field, which as Sean Gallardy said aren't documented and no-one's blogged about them (yet - so I don't want to get into details here). You can experiment with open transactions, crashing the server and moving the log file to force a database into the RECOVERY_PENDING state to watch which bits get set (again, no blog post about that yet).

Related Question