Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Solution to the MSSQL Server “Suspect”

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
3 May 2013CPOL2 min read 58.6K   6   4
Solution to the MSSQL Server “Suspect”

Background

When you see your database in Suspect mode, that is a code red situation. It's not something that you face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file). This post will show how to recover from suspect mode, but still go through SQL-server error logs and find out the root cause of the error.

Reason

At start-up, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process or if the file is missing, SQL Server starts displaying error.

In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.

Possible reason for changed to suspect mode in SQL Server can be:

  • The system cannot find the file specified during the creation/opening of physical device
  • Failed to open device where data or log file resides
  • SQL server went down/restarted in the middle of a transaction causes transactions log to be corrupted
  • SQL server cannot access data or log file while coming online, because of your beloved antivirus

Solution

To resolve this issue, run the commands listed below:

SQL
EXEC sp_resetstatus ‘DATABASE_NAME’;
ALTER DATABASE DATABASE_NAME SET EMERGENCY
DBCC checkdb(‘DATABASE_NAME’)
ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘DATABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASE_NAME SET MULTI_USER

What the above command does is perform a series of steps to check the logical & physical consistency of the database and tries to repair. The very first step is to turn off the suspect flag on a database, you can achieve this by using sytem stored procedure sp_resetstatus. Using this procedure, change the suspect flag to emergency. Once the database is set to EMERGENCY mode, it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it. Then, perform a consistency check by executing DBCC command on the master database. The next step is to rollback any transactions if any are present in the database and bring the database into Single User mode. Run the repair and finally bring database to Multi User mode.

Remember sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

If the database is still marked as suspect after performing these steps, there may be other problems preventing the database from recovering. At this point, the only option left is either restore from a good backup or set the database to emergency mode and use the bulk copy program copy the data out.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

Comments and Discussions

 
QuestionThanks a lot............it's Working....... Pin
naimesh21-Nov-14 22:14
naimesh21-Nov-14 22:14 
QuestionSolution worked quite fine Pin
Jean Pierre Stamm13-Jan-14 4:39
Jean Pierre Stamm13-Jan-14 4:39 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jun-13 20:48
professionalȘtefan-Mihai MOGA13-Jun-13 20:48 
GeneralRe: My vote of 5 Pin
Shahriar Iqbal Chowdhury/Galib14-Jun-13 5:45
professionalShahriar Iqbal Chowdhury/Galib14-Jun-13 5:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.