Sql-server – Programmatically determine whether a database was shut down cleanly or whether it required crash recovery

sql server

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.

Best Answer

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.