June 9, 2011 at 8:43 am
I have a database stuck in recovery mode, after a large delete operation was canceled and server ran out of disk space.
I am monitoring the SQL ERRORLOG, and seeing something like this:
...
2011-06-09 10:38:38.65 spid22s Recovery of database 'xxxx' (12) is 0% complete (approximately 108033 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2011-06-09 10:38:58.67 spid22s Recovery of database 'xxxx' (12) is 0% complete (approximately 108183 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2011-06-09 10:39:18.68 spid22s Recovery of database 'xxxx' (12) is 0% complete (approximately 108335 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2011-06-09 10:39:38.68 spid22s Recovery of database 'xxxx' (12) is 0% complete (approximately 108483 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
...
I'm seeing the estimated time for recovery to complete going up instead of down!
Is it hopeless waiting for this recovery to complete? It sure feels this way...:(
Is there anything I can do to speed this up or circumvent it? There is now enough free disk space allocated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2011 at 9:01 am
Wait. If you restart it will start over from scratch.
Check that something else (large disk copies, other database activity) isn't causing disk contention.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 9:22 am
Thank you, I'll check.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2011 at 12:07 pm
Database finally recovered after about 2 hrs.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2011 at 12:29 pm
Not too bad.
Either there was some incredibly long-running transaction that had to be rolled back, or there was some IO contention (or both)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 12:56 pm
GilaMonster (6/9/2011)
Not too bad.Either there was some incredibly long-running transaction that had to be rolled back, or there was some IO contention (or both)
I'm sure about the 1st one, and suspect the 2nd one also played a role: we have a file-backup app running in the background, and I suspect it's consuming a lot of resources.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply