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';