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?
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.
Aucun commentaire:
Enregistrer un commentaire