sqlrx logo
SQLRx Tip of the Month


January 2011
Greetings!

Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation!  Refer to the script below for help.

If you need help avoiding incidences such as this, please contact me regarding RemoteDBA services. It's also good practice to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks. 
___________________________________________________________________
SQL 2008SQL Server Administration:  You can try to save data by creating an empty database and copying tables to it after placing the database into EMERGENCY and then SINGLE_USER state. Most of the time, a suspect database has to be restored to be able to be used again. However, sometimes it is possible to repair a suspect database. I recently did just that for a client, and recovered 4 suspect databases.
 
Use the script below to check the integrity of the database or to repair it. Running an integrity check will tell you where the errors are and what the minimum repair you can do is. Always make sure you have database and transaction log backups so you can recover a failed database to point in time, if necessary.
___________________________________________________________ 

-- Attempt repair suspect database

USE master;

GO

-- Set database in EMERGENCY read only state

ALTER DATABASE MyDB SET EMERGENCY

GO

-- Set database in SINGLE_USER state

ALTER DATABASE MyDB SET SINGLE_USER

GO

 

USE MyDB

DBCC CHECKDB(MyDB) WITH NO_INFOMSGS  -- Run integrity Check

-- DBCC CHECKDB (MyDB, REPAIR_REBUILD) WITH NO_INFOMSGS  -- Repair database with no dataloss

-- DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS  -- Repair database with possible dataloss

GO

-- Set database in MULTI_USER state

ALTER DATABASE MyDB SET MULTI_USER

GO

 

 _________________________________________________________________

 
                            
SQLRx is a part of www.msdev.com, a Microsoft training site!  Check the site periodically to view new training videos.
 
If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month or msdev video, email it to us!  If you missed any of our previous tips, you can view them here.
 
For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].  

Sincerely,

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | [email protected] 
 
 
Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit
 
25 Years of Credibility & Trust
 

Join our email list          Check out our events          SQLRx Downloads