I had faced an interesting issue, during recent upgrade of SCCM. As you must be aware SCCM contains Database updates as well. Fix SCCM SQL DB SUSPECT mode.
Fix SCCM SQL DB SUSPECT Issue :-
During SCCM upgrade the SCCM (ConfigMgr) server (unexpectedly) got restarted. I still don’t know the reason of restart. After that restart I tried re running the R2 update and it got failed. When I checked SQL management studio, the CM_CAS DB is locked and it was in SUSPECT mode. Fix SCCM SQL DB SUSPECT mode.
Troubleshooting via Log files – Fix SCCM SQL DB SUSPECT mode
ConfigMgrSetup.log had loads of SQL errors. Fix SCCM SQL DB SUSPECT mode.
ERROR: SQL Server error: [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘ConfigMgr\Anoop’.
*** [42000][4060][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “CM_CAS” requested by the login. The login failed.
ERROR: SQL Server error: [42000][4060][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “CM_CAS” requested by the login. The login failed.
*** [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘ConfigMgr\Anoop’.
*** Failed to connect to the SQL Server, connection type: SMS ACCESS.
ERROR: SQL Server connection to ACNSQL.ConfigMgr.com failed.
Setup cannot start the SQL Server to perform the upgrade. Contact your SQL administrator.
When I checked SQL management studio, the CM_CAS DB is locked and it was in SUSPECT mode. I never ever seen or heard about this issue 🙁 So what’s next ? Google search hinted me this is common issue for SQL support teams. There are couple of steps to change from Suspect mode to Emergency. Once the DB is in emergency mode we can perform or try some recovery (with Data loss) options.
Following are the SQL commands which I talked about :-
- EXEC sp_resetstatus ‘YOUR_DB_NAME’;
- ALTER DATABASE YOUR_DATABASE_NAME SET EMERGENCY
- DBCC checkdb(‘YOUR_DB_NAME’)
- ALTER DATABASE YOUR_DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- DBCC CheckDB (‘YOUR_DB_NAME’, REPAIR_ALLOW_DATA_LOSS)
- ALTER DATABASE YOUR_DB_NAME SET MULTI_USER
Resolution :- Fix SCCM SQL DB SUSPECT mode
Resolution for this issue was something different. When I first saw the DB is in SUSPECT mode, I did a restart of SQL server and that didn’t make any difference. I did a restart again after 2-3 hours. Woo hooo :)!
The second restart of SQL server helped me. After the second restart the CM_CAS DB came back as single user mode. I changed it to multi user mode using my previous post. More details How to Fix the Error the database is in Single User Mode. Wow that is it. After that, SCCM 2012 R2 upgrade wizard ran successfully.
EXEC sp_resetstatus [CM_PS1];
ALTER DATABASE [CM_PS1] SET EMERGENCY
DBCC checkdb([CM_PS1])
ALTER DATABASE [CM_PS1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB([CM_PS1],REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [CM_PS1] SET MULTI_USER
After successfully executing this query my database”CM_PS1″ no longer be tagged as ‘suspect’ and now I am able to access the console.
Thanks for your suggestion.
EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
This is working Properly