FIX SCCM SQL DB SUSPECT Mode Issues ConfigMgr Endpoint Manager

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

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.

Fix SCCM SQL DB SUSPECT mode FIX SCCM SQL DB SUSPECT Mode Issues ConfigMgr Endpoint Manager
SCCM SQL DB SUSPECT FIX SCCM SQL DB SUSPECT Mode Issues ConfigMgr Endpoint Manager

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.

Patch My PC

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 :)!

1E Nomad

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

Free SCCM Training Part 1 | 17 Hours Of Latest Technical Content | ConfigMgr Lab HTMD Blog (anoopcnair.com)

How To Automatically Cleanup Intune Device | EndPoint Manager – HTMD Blog #2 (howtomanagedevices.com)

2 thoughts on “FIX SCCM SQL DB SUSPECT Mode Issues ConfigMgr Endpoint Manager”

  1. 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.

    Reply
  2. 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

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.