Tuesday, August 25, 2009

SQL 2000: Database in suspect mode.

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

1 comment:

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

    ReplyDelete

Using Azure Storage for SQL Backups

you may have taken backups on local storage, file servers, NAS, etc. Now a days it is becoming crucial you have backup copies available in...

Contact Me

Name

Email *

Message *