Sql Server 7 Administration Tips

Resolving Sql Server Database Suspect Problem:

---

All of us who work with SQL server (specially sql server 7 version) database frequently, might have faced the peculiar problem "Database Suspect" and black icon overlays the database icon in Enterprise Manager. Hence we can't access the Database. Here is the solution to resolve the problem. The concept is simply delete and rebuild the database log file.

Follow the following steps:

--------------------------

First of all delete the Database log (*.ldf) file of the suspected database. then

--1.) Reset the status from "Suspect':

EXEC sp_resetstatus ''

--2.) Set the database property "allow updates':

exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE

--3.) Set the database to "Emergency Mode":

UPDATE master..sysdatabases SET status=-32768 WHERE name=''

--4.) Rebuild the database log file:

dbcc rebuild_log ('','F:\MSSQL7\Data\_log')

For example: Suppose we have a Database Named "ContractorDatabase", which is suspected.

Now execute the following script, after deleting the existing database log file (ContractorDatabase_log.log) from Data file location (*:/MSSQL/Data).

EXEC sp_resetstatus 'ContractorDatabase'

EXEC sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE

UPDATE master..sysdatabases SET status=-32768 WHERE name='ContractorDatabase'

DBCC rebuild_log ('ContractorDatabase','F:\MSSQL7\Data\ContractorDatabase_log')