I am using MS SQL Server 2000 and applied SP3 and MSDE 8.0 is installed. In between any one of the database will be marked as suspect & no link or objects appears in it. The database becomes equivalent to being deleted. But after i restart the sql server, the database is normal & am able to access..After sometime another database gets marked as suspect & the procedure follows. Please help with a solution for this as this is happening 3 or 4 machines here..

Thanks & Regards,
Jona

CHeck the event viewer log, SQL server error log to findout why the database is getting in SUSPECT mode. I doubt on the disk subsytem or network issues on the server.

Articles on resolving the Suspect status:
http://support.microsoft.com/kb/889266
http://www.devx.com/vb2themax/Tip/18624
http://www.sql-server-performance.com/absolutenm/templates/?a=1249&z=1 (bit info on SQL 2000)

---

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

This process works for me, but this is probably to late to help you on this issue. 

Recovery of a Suspect Database in SQL 2005/2000First, you have to get all of the users to stop using or trying to use the applications that access the application, and keep them off until you are done.  You need exclusive usage of the bad database until you are done.  This effort may include temporarily changing application file names, restricting access to Web sites, etc.The following query should take the identified database from Suspect mode and repair it to Multi User mode.  Once that is accomplished you will then have to restore the database from the most recent SQL Backup file that you have available.Change MyDB to whatever database name that needs to be recovered.Depending on the size of the database this script can takes hours to run.  You can also run the commands individually, but keep them in the described order.

--   START of QUERY

Exec sp_resetstatus MyDBGo 

– This gives the SA rights to open this database regardless of issues

Alter Database MyDB Set EMERGENCYGo 

-- Validate the overall database integrity, this can take a while

DBCC Checkdb('MyDB') Go Alter database MyDB SET SINGLE_USER with ROLLBACK IMMEDIATEGo 

-- Force a repair even if you loss data, this can take even longer

DBCC Checkdb('MyDB', REPAIR_ALLOW_DATA_LOSS)Go Alter Database MyDB SET MULTI_USERGo Use MyDBGo 

-- Validate the system catalog integrity

DBCC CheckCatalogGo

-- END of QUERY

 Refresh the database server view or click on MYDB, to ensure that it is no longer in EMERGENCY or SUSPECT mode.If the database is still in Emergency or Suspect mode then it is not recoverable, and you will have to replace and rebuild it from older copies and backups of the database.

 

 Restoring a database from a SQL backup1)    Open the SQL Enterprise Manager2)    Login in as SA3)    Walk down to the database to be restored4)    Right Click on the database and mouse down to TASKS5)    Mouse down to RESTORE and over to FILES and FILEGROUPS (wait for it)6)    Ensure To and From database are set to the selected database7)    Select a Backup Set from the list, find the most recent FULL backup set and click the selection box.8)    On the left, select Options9)    Select the Restore Options: Overwrite the existing database10) Select the Restore State: Restore with RecoveryIf the Restore fails because it is in use, then select Restore with NonRecovery11) Click OKHow long this takes depends on the size of the database.  For example: dtReference takes a few minutes but the Production database takes hours.When this is done, do the same steps again but select the transaction backups since the full database backup date.

 

 Rebuilding the Indexes and Statistics of a DatabaseThis final step may not be needed, but it is the safest thing to do.