4

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.

Improve this question
3
  • 1
    It might be captured in one of the flag columns (like m_flagBits). I can think of two ways to be sure: (1) create scenarios where there is a log file missing and the database was or was not shut down cleanly, and inspect the boot page with a hex editor; (2) ask Paul. He's usually pretty responsive, especially with questions about his courses, as long as you are courteous when you ask.
    – Aaron Bertrand
    Commented Nov 3, 2016 at 3:22
  • 1
    Of course, there will be other clues, e.g. if you are recovering from corruption, you're going to try to recover, regardless of whether you know in advance if crash recovery will run. It might be useful to know 10 seconds earlier but are you really going to spend time finding that information instead of just proceeding with attempting to recover?
    – Aaron Bertrand
    Commented Nov 3, 2016 at 3:26
  • 1
    Fairly certain it's part of the dbi_status bits, which aren't documented.
    – Sean Gallardy
    Commented Nov 3, 2016 at 3:59

1 Answer 1

Reset to default
5

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.