Forum Discussion

Chengcheng210's avatar
Chengcheng210
Copper Contributor
Jun 21, 2020

database in suspect mode

Hi, there,

 

I'm experiencing a tough issue for me. When I was updating the some columns into the database I have previously install, the main memory corruption led the database jump into the suspect mode. I follow the following steps to recovery the database: 

1. EXEC sp_resetstatus 'dbNSFP';

2. ALTER DATABASE dbNSFP SET EMERGENCY;

3. DBCC CHECKDB('dbNSFP');

3. ALTER DATABASE dbNSFP SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

4. DBCC CHECKDB (dbNSFP, REPAIR_ALLOW_DATA_LOSS);

5. ALTER DATABASE dbNSFP SET MULTI_USER;

 

When I was in 1st step, MS SQL Server Management Studio gives:

 Warning: You must recover this database prior to access.

Completion time: 2020-06-21T11:33:19.6094013-04:00

 

When I was in 2nd step, the Studio reports error: 

Msg 5011, Level 14, State 7, Line 3
User does not have permission to alter database 'dbNSFP', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.

Completion time: 2020-06-21T11:34:47.5199727-04:00

 

I've been search this solution for several days. Some cues are pointing change the alter permission to the user. However, I'm a SA user, SA should hold all CONTROL permissions. I have been stuck here for days. Hope someone could help me out.

 

  • Mkelsawy's avatar
    Mkelsawy
    Copper Contributor
    Oct 18, 2020

    Hi Chengcheng210 ,

    The most important thing is never detach a suspect database. You can try to recover some data from a suspect database by using the emergency mode. But the only advisable solution would be to perform a database restore.

     

    also try first to restart SQL Server Engine service and check after database comes up again .

     

    last you need to check this tool (Stellar Repair) it used before by one of my colleagues and it's works (link removed by moderator)

  • Stacy Clark's avatar
    Stacy Clark
    Brass Contributor
    Feb 19, 2021

    Chengcheng210 

     

    Do you have admin rights? It seems like u don't have the rights. (as per the error message that you are getting after running step 2)

     

    I will suggest you restore the database from the updated backup copy. If the backup is not updated, make a copy of the MDF file and repair it using the DBCC CHECKDB repair option. 

    Any SQL repair tool would be the last and emergency resort to fix the corruption. I read an amazing review of SQL Repair Software by SQL MVP Grant Fritchy, a product advocate in RedGate Software. Check here: (link removed by moderator)

     

Resources