When a SQL Server database enters suspect mode, it becomes
inaccessible, indicating potential data corruption or resource issues. This
guide explains why databases enter suspect mode and provides actionable
recovery steps for common scenarios.
Why Does a Database Enter Suspect Mode?
A database may be marked as suspect due to:
Missing or unavailable database files (e.g., accidental
deletion).
Full transaction log or data file (no free space for
operations).
Corrupted database files (hardware failure, unexpected
shutdown).
Resource locks (OS or another process holding files
hostage).
How to Recover a Suspect Database
Below are recovery methods for three common scenarios:
Reason for suspect mode:
If one or more database files are not available.
2. If the entire database is not available.
3. If one or more database files are
corrupted.
4. If a database resource is being held by the operating system.
How to recover?
Ø Scenario 1: If the file is full
Execute sp_resetstatus.
Syntax: sp_resetstatus database_name
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database
Ø Scenario2: If the data file was damaged.
*
Take T.Log backup
*
Restore last Full backup
*
Restore T.Log backup
* Database comes online
Ø Scenario3: If the T.Log file was damaged
*
Take any user defined db for example: MyDB
*
Check the current location of files
sp_helpdb
MyDB
*
Stop server
*
Move the T.Log file into different folder
*
Start server --> DB goes into suspect mode
Select databasepropertyex ('mydb','status')
Steps to Recover:
Step1: Make the db into single user
1) Alter database mydb set Single_User
Step2: Set the db into emergency mode
2) Alter database mydb set Emergency
Step3: Run checkdb with required repair
level
3) DBCC CheckDB ('mydb', REPAIR_ALLOW_DATA_LOSS)
Step4: Set the db into multi user mode
4) Alter database mydb set Multi_User
No comments:
Post a Comment