DBCC DBRECOVER- Undocumented Command

Hi All,

Today I would like to share about DBCC DBRECOVER which is an undocumented command which I used many times. So thought of sharing, which incident made me to use this command and how it helped me.

Command(Syntax):

DBCC DBRECOVER('DATABASE NAME')

I would like to share two of my examples which made me to use this, will see the incidents now.

Incident-#1

All the datafiles on a particular filegroup has got filled there was no space to grow any more[DB shouldn't be designed like this, It was a non-prod box], during that time SQL Server itself brought the database to Suspect mode. It has written the following error in the SQL Server error logfile.

Error

Incident-#2

Before sql server loads completely some of the application started using the file[applications like virus scanner, System level backup software(like TSM) where exception are not set for the datafiles and logfiles] so when sql server comes up it has noted that it was in use so it marks the database as suspect. It has written the following error in the SQL Server error logfile.

Error

Possible Fix

Possible fix for the incidents noted above will have the following steps to get it fixed.

1. Find the Suspect Database

2. Recover the Suspect DB

3. Check the database Status

1. Find the Suspect Database

We can do it either in GUI or in TSQL.

In GUI the Database will look without Plus (+) symbol as shown below which shows no more action can be performed on that database & the database is suspect.

In TSQL it will work as given below

 

 

2. Recover the Suspect DB

We have found the DB in suspect mode and get that fixed, we can try the command which is shown below.

3. Check the database Status

We can do it either in GUI or in TSQL.

In GUI it looks as shown below.

In TSQL run the following command and you can find the status of the database.

This is how DBCC DBRECOVER helped me to recover my suspect DB, Hope it will be helpful to all the readers.