One of my database is trying to recover and wont stop!!
Is there anyway that I can stop this or delete the database?
Q. My SQL Server database is showing as "recovering". Why and what can IQuote:> One of my database is trying to recover and wont stop!!
> Is there anyway that I can stop this or delete the database?
A. Every time SQL Server starts up it recovers all databases so that all
transactions are either committed or rolled-back. This recovery process
normally only takes a few seconds/minutes, but it the server was terminated in
the middle of a long running update, then the recovery can take at least as
long as the update had taken so far - sometimes longer due to contention on the
Give it plenty of time to recover, but at the same time check the current and
previous errorlog files, and NT errorlogs, for any indications of what has
happened. If you've hit a hardware problem or SQL bug, then there WILL be
errors there to give an indication on what happened.
Check the physical disk activity lights on the server, and also check the
sysprocesses activity to see if the recovery task is using cpu and/or disk i/o.
Only on very rare occasions will SQL Server not recover the database correctly.
Additonally to check on recovery progress you could set trace flag 3412 which
writes an entry to the errorlog when each transaction is rolled forward or
If a database will not recover and you do NOT have a backup then you can use
the following trace flags to bypass recovery. If you use these then the
database/data may not be in a consistent state, but if you have no other choice
then use them and then immediately transfer out (using bcp or transfer tools)
all the objects you require.
3607 Skips automatic recovery for all databases.
3608 Skips automatic recovery for all databases except the master
If the database is still unavailable - marked as suspect - then issue the
following command to put the database into emergency mode (you'll need to allow
updates first). You can then go into the database (no need to restart SQL) and
extract out the data.
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
If all else fails, or you are unsure what to do, then don't hesitate to place a
call with Microsoft Product Support Services (PSS). They are there 24x7x365 to
deal with problems like this and the charge is nominal compared to the loss of
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
For SQL FAQ entries see
or www.ntfaq.com/sql.html (+ ntfaq download)
We have a database on SQL 6.5 that is trying to recover (for the last 3 days
or so !), but it never gets there. When I try to delete the database, I get
error 921 (please wait until database recovers fully).
So I would like to stop it recovering, delete it and load it again.
Any ideas ?
4. E! Editor
11. Stop Recovering