Database can go into suspect mode for a couple of reasons, including, corruption in data files, indexes, logs, bad storage, less space available on storage for the database files to grow. Following is one of the ways to fix the suspect databases. I followed this process on one of the production user databases.
Please read through before taking any action.
First you need to put your database into Emergency mode so that database can be accessed (though read only).
sp_configure 'allow updates', 1
Reconfigure with override
GO -- This will make system table editable.
--
update master..sysdatabases set status = status | 32768
where name = 'db'
GO --puts database into emergency mode
--
Once database is in emergency mode, try running the DBCC CHECKDB to check the database. If DBCC shows any error message, fix the error.
In case DBCC reports Log error, you may rebuild the log, however rebuilding the log will remove the t-log data:
DBCC REBUILD_LOG('db','c:\mssql\data\db.ldf')
--
In case of corrupted index, try creating the infected index again, you may try Create Index ... DROP_EXISTING
--
If nothing helps, you may run DBCC CHECKDB('DB',REPAIR_ALLOW_DATA_LOSS), however, this may certainly result in some data loss.
Once you have completed all the activities and database is fixed, you may run the following script to set database to normal mode.
update master..sysdatabases set status = 0 (Or the original value)
where name = 'db'
GO --puts database back into normal mode.
--
Sp_configure 'allow updates', 0
Reconfigure with override
GO -- This will make system tables un-editable.
--
Swarndeep
Subscribe to:
Post Comments (Atom)
-
DISCLAIMER: ANONYMOUS ACCESS IS NOT RECOMMENDED as it may give direct access to your report server or report objects to any one who knows th...
-
What is TDE ? Transparent Data Encryption (TDE) is used by Microsoft, Oracle, and other Database provider companies to encrypt data f...
-
Swarndeep Singh is a Database specialist with expertise in Performance Turning, Database Designing, Cloud Computing, Database Security, ...
If you are unable to repair database from suspect mode then you can use third party tool which can help you to easily repair and recover database from suspect mode. Read more: http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html
ReplyDeleteVery informative post. Thank you for sharing with us.
ReplyDeleteThirukkural pdf
Sai Satcharitra in English pdf
Sai Satcharitra in Tamil pdf
Sai Satcharitra in Telugu pdf
Sai Satcharitra in Hindi pdf