1

I have a ~300 GB database on a development server (SQL Server 2016 SP2 - Developer) that is stuck on DB STARTUP. I am unable to restore over or drop the database. Would shutting down the SQL Server service, deleting the files, and restarting the service work to bring the instance back online and leave me able to restore the database from a backup?

Backstory: After a failed application level data import the database was left in an inconsistent state. I restarted the SQL Server service and all other database except this one came back online. This is a development instance to I was able to leave it alone to see if the process ever completed. Two weeks later when I run sp_who2 there is still an entry for this database with the command DB STARTUP and ever increasing CPUTime and DiskIO. I now need to make this database available. Would deleting the files directly work or are there other options?

9
  • 1
    Is this a DB in Full recovery model and there was no transaction log backup taken? I'm suspecting it's rolling forward transactions and it may be single-threaded (does the SQL error log indicate roll-forward is in-process?)
    – MattyZDBA
    Oct 16 '18 at 21:04
  • 1
    Check error log to see how much % is completed in crash recovery.
    – Kin Shah
    Oct 16 '18 at 21:07
  • 1
    Does this show any negative spids sys.dm_tran_locks or blocking ? There has to be some error in error log as to why it is stuck.
    – Kin Shah
    Oct 16 '18 at 21:30
  • 3
    Well this won't fix the current problematic database but to get the backup restored you could try restoring as a different name WITH MOVE. You could also try waiting more than an hour and never, ever, ever just restart the SQL Server service as a knee-jerk fix. As you'll find out, this doesn't magically fix things like rebooting Windows. :-) Oct 16 '18 at 22:22
  • 2
    Stopping SQL Server and deleting the files will most likely cause the database fo go into "recovery pending" at next startup, and my experience is that I've never had any problems dropping a database in that state. ...in case you feel this is indeed an OK operation for you (restore from prior backup). Oct 17 '18 at 6:34
3

Stopping SQL Server and deleting the files will most likely cause the database to go into "recovery pending" at next startup, and my experience is that I've never had any problems dropping a database in that state.

Of course assuming that this is indeed an OK operation for you (restore from prior backup).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.