Technical Tip: EMS Database Suspect mode

Corruption can be caused by many issues, such as:

1. Unexpected Database Shutdown: If the database is shut down unexpectedly, trying to reach to database can initiate a recovery process but recovery may fail because of corrupted logs and data.
2.  Crash During Operations: SQL Server can crash while it is performing Daily Operations, and it can cause corruption. In addition to corruption databases also can encounter a suspect mode. Independent of the specific reason following steps can be applied to solve the problem and get the database back online.

The following example uses this case: 

To set emergency mode to fix a database suspected of issues:

To set up emergency mode in the suspected database, follow these steps:

1. Activate EMERGENCY Mode:

ALTER DATABASE 'FCM_Default' SET EMERGENCY 

Note:

If the database cannot be set to EMERGENCY state, make the database OFFLINE and then bring it ONLINE again.

2. Set Database into SINGLE_USER Mode: The EMERGENCY mode allows all users who have a sysadmin role to reach the database. However, to run the repair process, it is necessary to put the database into single-user mode. The following query can be used.

ALTER DATABASE 'FCM_Default' SET SINGLE_USER WITH ROLLBACK IMMEDIATE   

3. Backup the Database: It is always suggested to have a backup of the DB backup in a running state. This backup can be used just in case of any kind of recovery scenario if the procedure does not go as expected.
4. Perform Database Repair: The (REPAIR_ALLOW_DATA_LOSS) should be used with care since there is always the possibility of data loss. If corrupted records are found under the data page the entire record can be deleted. Use the following command to repair DB:

DBCC CHECKDB ('FCM_Default', REPAIR_ALLOW_DATA_LOSS)

5. Return to MULTI_USER Mode: To adjust MULTI_USER back the following query can be used:

ALTER DATABASE 'FCM_Default' SET MULTI_USER

6. Summary of commands used:

ALTER DATABASE 'FCM_Default' SET EMERGENCY

ALTER DATABASE 'FCM_Default' SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('FCM_Default', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE 'FCM_Default' SET MULTI_USER