Repair Suspect Database in SQL Server
Introduction
There can be a possibility that when you connect to an SQL Server
Instance you will find the database being marked as SUSPECT. During such
scenarios, you will not be able to connect to the database. In this article we
will go through the steps which you need to follow to recovery a database which
is marked as SUSPECT. The steps mentioned in this article works on SQL Server
2005 and higher versions.
Some of the reasons why an SQL Server database can be marked as
SUSPECT
1.
1. Database could have been corrupted.
2. There is not enough space available for the SQL Server to recover the database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
4. Database files are being held by operating system, third party backup software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.
2. There is not enough space available for the SQL Server to recover the database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
4. Database files are being held by operating system, third party backup software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.
Due to a hardware failure one of our database namely BPO was
marked SUSPECT when the SQL Server came back online. Already due to the
hardware failure we had downtime for more than two hours and adding to that
when the server came back online our mostly critical database was marked as
SUSPECT.
Steps to Recover a Database Marked as SUSPECT
1. 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
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO
2. Open the latest SQL Server Error Log and check for errors
logged for the database which is marked as suspect. You can open SQL Server
Error Log by expanding Management Node à SQL Server Error Logs. In my server I
could find below mentioned entries in SQL Server Error Logs.
Sample Error Messages
within SQL Server Error Log when database is marked as SUSPECT
Starting up database 'BPO'.
Error: 9003, Severity: 20, State: 9.
Error: 9003, Severity: 20, State: 9.
The log scan number (189624:16:2) passed to log scan in database
'BPO' is not valid. This error may indicate data corruption or that the log
file (.ldf) does not match the data file (.mdf). If this error occurred during
replication, re-create the publication. Otherwise, restore from backup if the
problem results in a failure during startup.
Error: 3414, Severity: 21, State: 1.
An error occurred during recovery, preventing the database 'BPO'
(database ID 10) from restarting. Diagnose the recovery errors and fix them, or
restore from a known good backup. If errors are not corrected or expected,
contact Technical Support.
CHECKDB for database 'BPO' finished without errors on 2009-12-15
11:30:28.320 (local time). This is an informational message only; no user
action is required.
3. 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
GO
ALTER DATABASE BPO SET EMERGENCY
GO
GO
Once the database is in EMERGENCY mode you will be able to query
the database.
4. Execute the DBCC CHECKDB command which will check
the logical and physical integrity of all the objects within the specified
database.
DBCC CHECKDB (BPO)
GO
GO
5. Next step will be to bring the user database in SINGLE_USER
mode by executing the below mentioned TSQL code.
ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
GO
6. Once the database is in SINGLE_USER mode execute the below TSQL
code to repair the database. When you repair your database using
REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of
data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option
of DBCC CHECKDB command then there is no way to go back to the previous state.
DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)
GO
GO
7. Finally, execute the below mentioned TSQL command to allow
MULTI_USER access to the database.
ALTER DATABASE BPO SET MULTI_USER
GO
GO
Conclusion
In this article you have seen how you can recover a database which
is marked SUSPECT.
No comments:
Post a Comment