Fix ConfigMgr Database Suspect Mode Issue | Repair Database

In this article, I will show you how to fix the ConfigMgr Database suspect mode issue. When the SQL database goes into suspect mode, SCCM becomes inaccessible. Although this is a serious problem, you can fix it by repairing the SQL database.

The ConfigMgr site database is a SQL Server database that contains data about your ConfigMgr infrastructure and objects, the client systems you manage, and other discovered resources. If the database is broken, you will not be able to access any ConfigMgr resource.

Due to a power outage, my ConfigMgr lab, which was running multiple VMs, abruptly crashed. As a result of the shutdown of all virtual machines, including the SCCM VM, the SQL database crashed. Due to a power outage, the SQL database went into “SUSPECT” mode, which made it impossible to use Configuration Manager. SCCM and SQL were running on a single server.

We’ll start by understanding what suspect mode is for SQL databases and why a database enters suspect mode. Later, I will cover the steps to recover the database from suspect mode and bring the ConfigMgr database into online mode.

When does SQL Database goes into suspect mode?

Most of the time, database administrators take care of recovering the SQL database, not Configuration Manager administrators. When your ConfigMgr database goes into suspect mode, you need to understand why this happens.

When SQL server suspects the primary file group of the database to be damaged or if the database file is missing, the database status is set to “Suspect“. In addition, there are range of errors that could result in SQL database in suspect mode which are listed below:

• SQL database gets damaged or corrupted due to power failure or sudden crash leading to suspect mode.
• The system fails to open the device where the data or log file of SQL server resides.
• A database goes into suspect mode when the SQL server crashes or restarts in the middle of a transaction, resulting in a corrupt or inaccessible transactions log file.
• SQL Server tries to open a database, and the file belonging to that database is already open by antivirus software installed on your system. Another reason could be the database files are being used by third-party backup software, etc.
• The database is terminated abnormally due to lack of disk space on the SQL Server.
• When the SQL cannot complete a rollback or roll forward operation, it goes into suspect mode.

Configuration Manager Console Fails to Connect to the Site

The first sign that ConfigMgr Database suspect mode is a problem is that the SCCM console cannot connect to the site. When you launch the ConfigMgr console, you’ll see the following error: “Configuration Manager cannot connect to the site.” This is a very generic error, and you’ll have to review the ConfigMgr admin console logs to determine the reason for the error.

Performing Site Maintenance or Site Reset Fails

When a ConfigMgr console fails to connect to the site, you typically perform a site reset. The Site Reset is feature in SCCM that helps you to re-apply the default files and registry permissions on the site server. It also re-installs each Configuration Manager component.

When you are dealing with ConfigMgr database suspect mode issue, you cannot perform the site maintenance or reset the site. That’s because the ConfigMgr SQL database is in suspect mode and this will prevent the site reset operation.

The ConfigMgr site reset fails with the following error when the SQL database is in suspect mode. Setup cannot find a valid site control image in the database or in the site control file.

The ConfigMgrSetup.log is an important file that tracks the site reset process in SCCM. You can open SCCM log files with log file viewer tools. Clicking View Log button opens ConfigMgrSetup.log in CMTrace log file viewer tool.

The following errors were logged in the ConfigMgrSetup.log during the ConfigMgr Database suspect mode issue.

• Cannot open database “CM_MEM” requested by the login. The login failed.
• Failed to connect to the SQL Server, connection type: SMS ACCESS.

*** [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'MECMLAB\cmadmin'.
ERROR: SQL Server error: [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'MECMLAB\cmadmin'.
*** [42000][4060][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "CM_MEM" 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_MEM" requested by the login. The login failed.
*** [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'MECMLAB\cmadmin'.
ERROR: SQL Server error: [28000][18456][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'MECMLAB\cmadmin'.
*** [42000][4060][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "CM_MEM" 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_MEM" requested by the login. The login failed.
*** Failed to connect to the SQL Server, connection type: SMS ACCESS.
CSiteSettings::ReadActualSCFFromDatabase: Failed to get sql connection
ERROR: Failed to retrieve verifiable site control data
ERROR: Failed to read site control data.
~===================== Failed Configuration Manager Server Setup - Modify Site Settings =====================

Determine if SCCM Database is in Suspect Mode

To determine if the SCCM database is in suspect mode, perform the following steps:

• Launch the SQL Server Manager Studio.
• Connect to database engine with your credentials.
• Expand Databases and select Configuration Manager Database.
• CM_MEM (Suspect) confirms the SCCM database in suspect mode.

When the SCCM database is in suspect mode, you cannot perform any activities on the database. You cannot run any queries as it throws an error “The database CM_MEM is not accessible (ObjectExplorer).”

Review the Error Logs on SQL Server

Once you have determined that the ConfigMgr database is in suspect mode, you can review the SQL error logs to determine critical errors. In the SSMS, you can expand Management > SQL Server Logs and review the logs. We see the following errors on the SQL logs:

• Login failed for user ‘NT AUTHORITY\SYSTEM‘. Reason: Failed to open the explicitly specified ConfigMgr database.
• Error 18456, Severity: 14, State: 38
• An error occurred during recovery, preventing the database from restarting.

All the above errors indicate that the ConfigMgr database is inaccessible when the SQL database is in suspect mode. The only solution is to recover MS SQL database from suspect mode.

Fix ConfigMgr Database Suspect Mode Issue

Let’s go through the steps to resolve the ConfigMgr database suspect mode issue. The steps mentioned in this article works on SQL Server 2012 and all higher versions.

Step 1: Check the SQL Server Version

We will use the sqlcmd utility which is a command-line utility for ad-hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks.

You can use multiple ways to determine the SQL Server version. Run the following command to determine the SQL Server version:

Select @@version
Go

Step 2: Identify Databases in Suspect Mode

Execute the below-mentioned TSQL code to identify all the databases which are marked as SUSPECT.

USE master
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO

Step 3: Bring Database in Emergency Mode

When a database is in SUSPECT mode you will not be able to get connected to the database. Hence, you need to bring the database first in EMERGENCY mode to repair the database. Execute the below-mentioned TSQL code to bring the database in EMERGENCY mode.

USE master
GO
ALTER DATABASE CM_MEM SET EMERGENCY
GO

Once the database is in EMERGENCY mode you will be able to query the database.

Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (CM_MEM)
GO

Step 4: Bring Database in SINGLE_USER Mode

Bring the user database in SINGLE_USER mode by executing the below-mentioned TSQL code.

ALTER DATABASE CM_MEM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Step 5: Repair the SQL Database

Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database.

DBCC CHECKDB (CM_MEM, REPAIR_ALLOW_DATA_LOSS)
GO

Step 6: Allow MULTI_USER Access to Database

Execute the below-mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE CM_MEM SET MULTI_USER
GO

After performing the above steps, the ConfigMgr Database suspect mode issue was successfully resolved.