Programmatically determine whether a database was shut down cleanly or whether it required crash recovery

Question

There are various ways to shut down the SQL Server service (I can think of at least: SHUTDOWN, SHUTDOWN WITH NOWAIT, stopping the service (including bugs), killing the process, power loss).

An unclean database shutdown causes crash recovery to run when the server starts up again. I would like to track events when crash recovery was needed. I'd like to look into those cases.

How can I query a running SQL Server instance to see what databases underwent crash recovery on the last server startup (or the last time they were brought online)? In order to automate this the query must not be a manual procedure.

I did not find anything in sys.databases. In particular, is_cleanly_shutdown apparently does not represent what I'd like to find out.

Answer 1

Crash recovery will be used every time SQL Server is restarted. So I am assuming that when you take it offline cleanly means no transactions are rolled back. That information can be found in the error log. We can write code to read the error log based on timestamp and read the logs to see which database transactions were rolled back

EX:

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2015-01-05 01:15:48.39 Server      Software Usage Metrics is disabled.
2015-01-05 01:15:48.39 spid4s      Starting up database 'master'.
2015-01-05 01:15:48.50 spid4s      140 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2015-01-05 01:15:48.50 Server      CLR version v4.0.30319 loaded.
2015-01-05 01:15:48.60 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2015-01-05 01:15:48.60 spid4s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.

Answer 2

Database recovery can occur for the following reasons that I know of:

• SQL Server service restart/start as each database will be initialized or brought online
• Restore a database
• Attach a database
• A database has AUTO_CLOSE enabled.

The recovery phases of a database are logged to the log(s) for SQL Server. However since by default there are 6 logs for a given instance you will have to iterate through each one to find the information you are looking for, or more based on the configuration. A restart will cause a new log to be generated so if multiple restarts occur (which I have had this occur) you may get by only checking the last few logs instead of all of them. This may also depend on how often you are running this check.

You can query sys.dm_server_services to determine when a service last started, or check the created date of tempdb.

A restore can be checked via msdb.dbo.restorehistory.

An attach would be via the logs, don't think this is logged in the default trace but it might.