What’s the official successor for sp_resetstatus in case of a SUSPECT database?

Posted on

Question :

I have a database that has been marked SUSPECT because the volume that both the data and the log file were on suddenly disappeared (an accident – this is a developer workstation). I tried bringing the database back online:

ALTER DATABASE D SET ONLINE

That did nothing. On the web the procedure sp_resetstatus is being recommended but it appears to be obsolete.

I do not wish to run DBCC CHECKDB, at least not right now. The database is very big. What is the official way to bring a SUSPECT database online as quickly as possible?

Answer :

As of SQL Server 2012, sp_resetstatus is still supported. However, Paul Randal has some excellent counsel, including do not detach that database.

The first and best answer is to restore a good backup.

If that does not work and you can retrieve the files from somewhere (where?), check out Paul’s posts at:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Since these are detailed discussions, I will not rehash them. But read carefully.

Given that the database is physically intact you can bring it online using

ALTER DATABASE DatabaseName SET EMERGENCY
ALTER DATABASE DatabaseName SET ONLINE

These statements execute very quickly. They just seem to remove the SUSPECT status and start recovery if necessary.

Leave a Reply

Your email address will not be published.