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: