1

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.
2

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

EDIT: How do i tell when a database is ONLINE but still in the UNDO recovery stage?

Well easiest method is reading errorlog it would have information like

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

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

5
  • I was trying to detect when the db state was online but still in recovery phase 3. Unfortunately I can't address the restart cause (3rd party IT). Our application has a Windows service dependency on SQL server. Once SQL Server is up, the application will startup and tries to query the database. I wanted to delay until phase 3 is complete. – Tristan Mar 5 '15 at 7:08
  • Assuming you have Ent edition even if DB comes online after 2nd phase and if you are query is not blocked by locks held during undo you might succeed. The delay can be done from application side for which I am not aware – Shanky Mar 5 '15 at 7:35
  • I need to handle Enterprise edition. The idea was to delay the application from starting up, that way, random query X doesn't possibly timeout. – Tristan Mar 5 '15 at 7:54
  • I have edited my answer for your specific question – Shanky Mar 5 '15 at 8:07
  • I was hoping for a flag in a system view. I'll try poking the error log for the 'Recovery completed for database TestDB' line. Thanks. – Tristan Mar 6 '15 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.