stop recovering database

stop recovering database

Post by Mick » Sat, 14 Aug 1999 04:00:00


One of my database is trying to recover and wont stop!!

Is there anyway that I can stop this or delete the database?



stop recovering database

Post by Neil Pik » Sun, 15 Aug 1999 04:00:00


Quote:> 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 I
(v1.2  15.10.1998)

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
log device.

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
your data!

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 For SQL FAQ entries see

 or (+ ntfaq download)


1. Stop a Database from Recovering

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 ?

David Noble.

2. What am I doing wrong with SELECT?

3. Stop database recovering

4. E! Editor

5. stopping a recovering database.

6. Question Regarding Backup Tables, Transaction Tables

7. Database in 'Recovering' mode never recovers

8. Find Oracle Instance Names in UNIX Server

9. Replication stops with exception and takes very long to recover

10. Stopping a "Recovering" full-text index

11. Stop Recovering

12. ANNOUNCEMENT: Debian GNU Linux package to start/stop database(s)+listener(s) on system start/stop

13. database snapshot and recovering into another database