Analysis of a 400GB database after SQL server restarts takes forever (or maybe hang)

Hello there,

We have a database used by a thrid party application that is 400GB in size. After the server gave some warnings, we restarted the machine and SQL server is doing an analysis on the database so it can be recovered. The recovery analysis of the 400GB database has been run for 3 days and only 7% complete. I doubt it'll ever finish. Because this recovery analysis is a background process which I can not kill, I can not drop the database either because it places an exclusive lock on the database when SQL server is doing analysis. We're using SQL server 2000 enterprise edition, sp3. Now I think that we probably should not have such a big database in the first place. Right now, I just want to stop the analysis if I can so I can either truncate the tables in that 400GB or drop the database completely. Not sure if start sql server in cmd with single user mode can do or not. Any suggestions are appreciated.

thanks,

-Jessie

if the database is not important and you do have a copy of it backed up, you can terminate sqlserver service; start a new session in console mode and drop the database.

e.g.

sqlservr.exe -c -f -m

|||

Thanks OJ for your reply! This database is important but we can't afford every time the server restarts, it takes 1 day to only recover 3% of this db. That "-f" stops the recovery of the user databases. But when I tried to run "use 400GBdb", it gave me this error:Server: Msg 913, Level 16, State 4, Line 1
Could not find database ID 12. Database may not be activated yet or may be in transition.

I'll first back the data/log files to tape before I drop the db. But are you sure I can drop the db even the system can not find the dbid?

-Jessie

|||

Jessie,

You can start sqlserver without doing any recovery of any user databases.

e.g.

sqlservr.exe -s"your_instance" -f -m

then connect to the instance and drop the database

drop database "your_db"

|||

Jessie,

You can start sqlserver without doing any recovery of any user databases.

e.g.

sqlservr.exe -s"your_instance" -f -m

then connect to the instance and drop the database

drop database "your_db"

|||

Thanks OJ! Yes, using -f can stop recovery of user database. But because user db is not recovered, they are not accessible. You'll get:dbid not found error. I rebooted the SQL serve twice and both recovery of the 400GB hung when the recovery is 99% complete. From SQL server error log: The Scheduler 3 appears to be hung. SPID 17, ECID 0, UMS Context 0x03716938. I guess 400 GB is beyong SQL server 2000 limit.

What I did is that I stopped the SQL server, delete the log file and restarted the SQL server. Then the db is marked as suspect and I deleted the db.