FIX SCCM SQL DB SUSPECT Mode Issues ConfigMgr Endpoint Manager. I had faced an interesting issue during the 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 Mode Issue
The SCCM upgrade, the SCCM (ConfigMgr) server (unexpectedly) got restarted. I still don’t know the reason for restarting. After that restart, I tried re-running the R2 update, and it failed. When I checked SQL management studio, the CM_CAS DB was 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 was locked, and it was in SUSPECT mode. I have never seen or heard about this issue š So what’s next? Google search hinted that this is a common issue for SQL support teams. There are a couple of steps to change from Suspect mode to Emergency. Once the DB is in the 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
The resolution for this issue was something different. When I first saw the DB was in SUSPECT mode, I restarted the SQL server, which didn’t make any difference. I did a restart again after 2-3 hours. Woo hoo :)!
The second restart of the 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.
Resources
Author
AnoopĀ isĀ Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…
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