database in suspect mode

%3CLINGO-SUB%20id%3D%22lingo-sub-1479514%22%20slang%3D%22en-US%22%3Edatabase%20in%20suspect%20mode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479514%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20experiencing%20a%20tough%20issue%20for%20me.%20When%20I%20was%20updating%20the%20some%20columns%20into%20the%20database%20I%20have%20previously%20install%2C%20the%20main%20memory%20corruption%20led%20the%20database%20jump%20into%20the%20suspect%20mode.%20I%20follow%20the%20following%20steps%20to%20recovery%20the%20database%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20EXEC%20sp_resetstatus%20'dbNSFP'%3B%3C%2FP%3E%3CP%3E2.%20ALTER%20DATABASE%20dbNSFP%20SET%20EMERGENCY%3B%3C%2FP%3E%3CP%3E3.%20DBCC%20CHECKDB('dbNSFP')%3B%3C%2FP%3E%3CP%3E3.%20ALTER%20DATABASE%20dbNSFP%20SET%20SINGLE_USER%20WITH%20ROLLBACK%20IMMEDIATE%3B%3C%2FP%3E%3CP%3E4.%20DBCC%20CHECKDB%20(dbNSFP%2C%20REPAIR_ALLOW_DATA_LOSS)%3B%3C%2FP%3E%3CP%3E5.%20ALTER%20DATABASE%20dbNSFP%20SET%20MULTI_USER%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20was%20in%201st%20step%2C%20MS%20SQL%20Server%20Management%20Studio%20gives%3A%3C%2FP%3E%3CP%3E%26nbsp%3BWarning%3A%20You%20must%20recover%20this%20database%20prior%20to%20access.%3C%2FP%3E%3CP%3ECompletion%20time%3A%202020-06-21T11%3A33%3A19.6094013-04%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20was%20in%202nd%20step%2C%20the%26nbsp%3BStudio%20reports%20error%3A%26nbsp%3B%3C%2FP%3E%3CP%3EMsg%205011%2C%20Level%2014%2C%20State%207%2C%20Line%203%3CBR%20%2F%3EUser%20does%20not%20have%20permission%20to%20alter%20database%20'dbNSFP'%2C%20the%20database%20does%20not%20exist%2C%20or%20the%20database%20is%20not%20in%20a%20state%20that%20allows%20access%20checks.%3CBR%20%2F%3EMsg%205069%2C%20Level%2016%2C%20State%201%2C%20Line%203%3CBR%20%2F%3EALTER%20DATABASE%20statement%20failed.%3C%2FP%3E%3CP%3ECompletion%20time%3A%202020-06-21T11%3A34%3A47.5199727-04%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20search%20this%20solution%20for%20several%20days.%20Some%20cues%20are%20pointing%20change%20the%20alter%20permission%20to%20the%20user.%20However%2C%20I'm%20a%20SA%20user%2C%20SA%20should%20hold%20all%20CONTROL%20permissions.%20I%20have%20been%20stuck%20here%20for%20days.%20Hope%20someone%20could%20help%20me%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791924%22%20slang%3D%22en-US%22%3ERe%3A%20database%20in%20suspect%20mode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791924%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706002%22%20target%3D%22_blank%22%3E%40Chengcheng210%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20here%20to%20suggest%20one%20of%20the%20best%20solutions%20that%20can%20recover%20your%20SQL%20server%20database%20from%20suspect%20mode.%20This%20professional%20tool%20named%3CA%20href%3D%22https%3A%2F%2Fwww.arysontechnologies.com%2Fsql-database-recovery.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%3CSTRONG%3E%20SQL%20Server%20Recovery%3C%2FSTRONG%3E%20%3C%2FA%3Ecan%20repair%20and%20recover%20your%20SQL%20database%20from%20suspect%20mode.%20This%20tool%20has%20the%20ability%20to%20recover%20your%20database%20without%20affecting%20the%20original%20database.%20You%20can%20use%20this%20professional%20tool%20as%20per%20your%20requirements.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1793895%22%20slang%3D%22en-US%22%3ERe%3A%20database%20in%20suspect%20mode%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1793895%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706002%22%20target%3D%22_blank%22%3E%40Chengcheng210%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EThe%20most%20important%20thing%20is%20never%20detach%20a%20suspect%20database.%20You%20can%20try%20to%20recover%20some%20data%20from%20a%20suspect%20database%20by%20using%20the%20emergency%20mode.%20But%20the%20only%20advisable%20solution%20would%20be%20to%20perform%20a%20database%20restore.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%20try%20first%20to%20restart%20SQL%20Server%20Engine%20service%20and%20check%20after%20database%20comes%20up%20again%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elast%20you%20need%20to%20check%20this%20tool%20(%3CSTRONG%3E%3CEM%3EStellar%20Repair%3C%2FEM%3E%3C%2FSTRONG%3E)%20it%20used%20before%20by%20one%20of%20my%20colleagues%20and%20it's%20works%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.stellarinfo.com%2Fblog%2Frecover-sql-database-from-suspect-mode%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.stellarinfo.com%2Fblog%2Frecover-sql-database-from-suspect-mode%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

3 Replies

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)

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

 

Hi,
This tool worked for me. Thank you so much for sharing.