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..
Solution:
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 master database.
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 database.
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 to EMERGENCY.
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.
DBCC checkdb('test_dr')
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 intact.
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:
