Very Computer

Hi,

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

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

Cheerz

Micky,

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
do?
(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
back.

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

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 www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.swynk.com/faq/sql/sqlserverfaq.asp
 or http://www.sql-server.co.uk