While I was working on a troubleshooting scenario, I was asked for a way to track whether database is shutdown already (Auto closed)
Currently SQL Server only prints a message in error log when database startup happens not when it is shutdown.
The database startup message would be something like this:
Starting up database 'Database Name'.
I found two simple ways to track database shutdown:
1. Collation Name for a database will be NULL if it is already shutdown. So from the query given below, a database with NULL in the collation name is currently auto closed:
SELECT name, collation_name FROM sys.databases
2. DBTABLE structure is destroyed when a database is shutdown. So running the query below will return no results if the database is already shutdown:
DBCC DBTABLE('DBNAME') WITH TABLERESULTS
