|
Corruption can be caused by many reasons such as:
- 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.
- 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 below example uses this case:
To set emergency Mode to Fix a Suspect Database
To set up emergency mode in the suspected database following steps can be used.
- 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.
- 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
- 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.
- 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)
- Return to MULTI_USER Mode: To adjust MULTI_USER back the following query can be used:
ALTER DATABASE 'FCM_Default' SET MULTI_USER
- Used command summary below:
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
|
