Recovering a SQL Server Database from Suspect Mode
A couple of days back at I
got a call from my support team informing me that one of our database located
on the Production Server went into Suspect
Mode. The version used was SQL Server 2005 Service Pack 3. Being a
Production Database server, it was a Priority 1 incident and the expected time
of resolution was 4 hours..
The first step was to identify why this incident occured and after investigation it was found that it was due to the corruption of the transactional log file of the database.
I connected to SSMS using the sa login
credentials and located the SUSPECT database:
I then reset the status of the SUSPECT Database by executing the below T-SQL query against the
EXEC sp_resetstatus 'test_dr';
sp_resetstatus turns off the suspect flag on a
database. This procedure updates the mode and status columns of the named
database in sys.databases. Also note that only logins having
sysadmin priveleges can perform this :
As you can see in the above screen capture, the T-SQL query
gave the warning message upon execution:
You must recover this database prior to access
The next step was to set the SUSPECT database into an EMERGENCY
mode. This was done by executing the below SQL query against the master
ALTER DATABASE test_dr SET EMERGENCY
Once the database is set to EMERGENCY mode it becomes a READ_ONLY
copy and only members of sysadmin fixed server roles have privileges to
access it. The basic purpose for this is to facilitate troubleshooting. I did not want
other users updating the database while it was being worked on.
As you can see from the above screen capture, once the T-SQL
query got executed successfully the state of the database changed from SUSPECT
Once the database state was changed to EMERGENCY. I
then performrf a consistency check by executing the below T-SQL query
against the master database.
Which resulted in the below output:
As seen from the above screen capture
there is no issue with respect to consistency of the test_dr database.
Also, this confirmed that the logical and physical integrity of the database was
The next step was to set the database to SINGLE USER
mode with ROLLBACK IMMEDIATE. To do this the below SQL query was
executed against the master database.
ALTER DATABASE test_dr SET SINGLE_USER WITH ROLLBACK IMMEDIATE
The above query will rollback any transactions if any are present
in the test_dr database and will bring the database named test_dr
into Single User mode.
Please refer to the screen capture below:
Pages: 1 2