Advertisement

ConfigMgr SCCM 2012 R2 Upgrade put SQL DB into SUSPECT mode

I had faced an interesting issue, during recent upgrade of SCCM ConfigMgr 2012 R2. As you must be aware SCCM 2012 R2 contains Database updates as well.

Issue :-

During SCCM 2012 R2 upgrade the SCCM (ConfigMgr) 2012 SP1 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.

SCCM 2012 R2 DATABASE is in SUSPECT MODE

SCCM 2012 R2 DATABASE is in SUSPECT MODE

More details on troubleshooting via Log files :- 

ConfigMgrSetup.log had loads of SQL errors.

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

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.

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), SCCM 2012, SCCM 2012 R2, System Center 2012 Configuration Manager

Leave a Comment and Contact Anoop