I'm having trouble working out when a database is 'ready' after an unscheduled SQL Server restart.
If there was a large transaction running when the server was restarted the database will go into RECOVERY and I can continually check sys.databases.state until it is ONLINE.
However ONLINE just isn't enough. If the uncommitted transaction is sufficiently large enough the database comes ONLINE, but recovery is still occurring in the background.
At ~10:07:48 the database is ONLINE and accepting connections with the initial catalog set. However, some queries will timeout as the recovery hasn't completed.
The contents of sys.dm_tran_locks shows 857k rows.
Is there way to check that a database has finished the recovery completely?
EDIT: How do i tell when a database is ONLINE but still in the UNDO recovery stage?
SQL Error Log:
2015-03-05 10:06:54.92 spid9s Starting up database 'model'.
2015-03-05 10:06:54.95 spid9s Clearing tempdb database.
2015-03-05 10:06:55.07 spid9s Starting up database 'tempdb'.
2015-03-05 10:06:55.10 spid18s Recovery of database 'TestDB' (5) is 0% complete (approximately 4747 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:09.20 spid18s Recovery of database 'TestDB' (5) is 0% complete (approximately 5852 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:09.21 spid18s Recovery of database 'TestDB' (5) is 0% complete (approximately 10315 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:09.59 spid18s Recovery of database 'TestDB' (5) is 1% complete (approximately 1314 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:13.06 Logon Error: 18456, Severity: 14, State: 38.
2015-03-05 10:07:13.06 Logon Login failed for user 'DOMAIN\user'. Reason: Failed to open the explicitly specified database 'TestDB'. [CLIENT: <local machine>]
2015-03-05 10:07:20.20 Logon Error: 18456, Severity: 14, State: 38.
2015-03-05 10:07:20.20 Logon Login failed for user 'DOMAIN\user'. Reason: Failed to open the explicitly specified database 'TestDB'. [CLIENT: <local machine>]
2015-03-05 10:07:21.64 spid18s Recovery of database 'TestDB' (5) is 2% complete (approximately 1300 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:32.35 Logon Error: 18456, Severity: 14, State: 38.
2015-03-05 10:07:32.35 Logon Login failed for user 'DOMAIN\user'. Reason: Failed to open the explicitly specified database 'TestDB'. [CLIENT: <local machine>]
2015-03-05 10:07:35.15 spid18s Recovery of database 'TestDB' (5) is 3% complete (approximately 1294 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.76 spid18s Recovery of database 'TestDB' (5) is 3% complete (approximately 1281 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.78 spid18s Recovery of database 'TestDB' (5) is 3% complete (approximately 1281 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.99 spid18s CHECKDB for database 'TestDB' finished without errors on 2013-08-13 21:40:44.543 (local time). This is an informational message only; no user action is required.
~DATABASE ONLINE
2015-03-05 10:07:48.03 spid5s Recovery of database 'TestDB' (5) is 24% complete (approximately 163 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:48.47 spid5s Recovery of database 'TestDB' (5) is 51% complete (approximately 49 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
.... Many more 'Recovery of database' lines
2015-03-05 10:09:22.86 spid5s Recovery of database 'TestDB' (5) is 99% complete (approximately 1 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2015-03-05 10:09:25.52 spid5s 1 transactions rolled back in database 'TestDB' (5:0). This is an informational message only. No user action is required.
2015-03-05 10:09:25.52 spid5s Recovery is writing a checkpoint in database 'TestDB' (5). This is an informational message only. No user action is required.
2015-03-05 10:09:30.13 spid5s Recovery completed for database TestDB (database ID 5) in 155 second(s) (analysis 14126 ms, redo 38547 ms, undo 97737 ms.) This is an informational message only. No user action is required.
2015-03-05 10:09:30.15 spid5s Recovery is complete. This is an informational message only. No user action is required.
Best Answer
There are couple of things to note here. The database will only be accessible by query seamlessly and would allow connection if all three phases of recovery has completed. The three phases are analysis, redo and Undo(sometimes if DB is in consistent state after redo an UNDO phase would not be required). In enterprise edition the database would come online after second phase (this is called Fast recovery which was introduced from SS 2005 onwards) and you would see database as online but still internally third phase, the UNDO, phase is going on and would be accessible completely only after third phase completes. What I mean by completely is For fast recovery, a query may bump into one of the locks being held to allow fast recovery – in which case it will have to wait for that lock to be dropped as UNDO progresses.Please read more about Fast recovery
In standard edition database would only come online after all three phases have completed.
You can read more about three phases of recovery
You can check status of database using sys.databases view
select state from sys.databases
In errorlog if it is written for particular database that Recovery is complete then you can be assured that database is online and can be accessed
EDIT
Well easiest method is reading errorlog it would have information like
If you look at logs you could see information related to what phases completed
I would say you should not worry about when database would come online because in any case it will always follow three phases of recovery (when undergoing crash recovery) you should focus on why Unexpectedly SQL server restarted. This should be avoided at all cost. Make sure SQL Server is patched to latest service pack