Aug 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

2 comments:

Optimizing Indexes with Execution Plans

Contact Me

Name

Email *

Message *