SQL Server 2016 – Resolving Database Stuck on DB STARTUP

sql serversql-server-2016

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?

Best Answer

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

Related Solutions

Sql-server – Starting up sql server after hard-drive recovery

You can try attaching the MDF file to the new instance, but there is no guarantee that it will work. If the machine crashed while SQL Server was running, the files may actually be corrupt. If you don't have proper backups, you can try:

CREATE DATABASE tms ON 
(
  name = tms_Data, 
  filename = 'e:\path\tms_data.mdf' -- <-- move this file to the data location on new box
)
FOR ATTACH_REBUILD_LOG;

If that doesn't work, this will probably be a very expensive lesson about why we want to take backups regularly and store them in a different location.

SQL Server 2016 SP2 Installation Failure – Troubleshooting Guide

I've seen this error on other versions of SQL Server on a legacy build.

In my case, it was caused by the owner of a role or schema was not the default: Something like db_datareader was authorised by a user account (they no longer have sysadmin)

How to recover?

Could not restore master because the master database version was lower than the binaries version
Created a new install with the correct version to get it running, then restored master to that, then copied master mdf back to the original install.

Unfortunately, I can't remember the exact details now.
It's the only time in 20 years of SQL Server I've...

seen a service pack install fail that stops SQL Server running
had to deal with a version mismatch on restore of master

Best Answer

Related Solutions

Sql-server – Starting up sql server after hard-drive recovery

SQL Server 2016 SP2 Installation Failure – Troubleshooting Guide

Related Question