Solution To The MSSQL Server “Suspect”

Rate this item
(0 votes)

Background When you see the your database in Suspect mode that a code red situation. Its 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 mod

Background

When you see the your database in Suspect mode that a code red situation. Its 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 start 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 can not access data or log file while coming online , because of you beloved antivirus
Solution

To resolve this issue run the commands listed below,
 
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 do is perform a series of step to check the logical & physical consistency of the database and tries to repair. Very first step is to turns off the suspect flag on a database, you can achieve his 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.  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, 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.

Reference: http://www.codeproject.com/Articles/587460/Solution-To-The-MSSQL-Server-Suspect

Last modified on Friday, 17 July 2015 10:03
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

3 comments

  • Comment Link Enid Wednesday, 21 June 2017 09:46 posted by Enid

    I drop a comment each time I like a post on a site or if I have something to contribute
    to the discussion. It's triggered by the sincerness
    communicated in the article I read. And after this post Solution To The MSSQL Server Suspect - MSSQL Server Repair - Data Recoup Services.
    I was actually excited enough to post a thought :-P I actually
    do have 2 questions for you if it's allright.
    Is it just me or do a few of these remarks come across like they
    are written by brain dead individuals? :-P And,
    if you are writing at other online sites, I'd like to keep up with you.
    Could you list every one of all your public sites like your
    linkedin profile, Facebook page or twitter feed?

  • Comment Link Franchesca Tuesday, 09 May 2017 11:26 posted by Franchesca

    Right here is the perfect web site for everyone who hopes
    to find out about this topic. You know so much its almost
    hard to argue with you (not that I personally would want to?HaHa).
    You certainly put a new spin on a topic that has been written about for years.
    Excellent stuff, just wonderful!

  • Comment Link Janessa Friday, 16 December 2016 20:42 posted by Janessa

    When someone writes an piece of writing he/she retains the image of a user in his/her mind that how
    a user can know it. Thus that's why this piece of writing is amazing.

    Thanks!

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.