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..

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:

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |