SQL Server Performance

Suspect in SQL Server Database

Discussion in 'General DBA Questions' started by jonageorge, May 18, 2006.

  1. jonageorge New Member

    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
  2. satya Moderator

    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.
  3. slamotte New Member

    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 QUERYExec sp_resetstatus MyDBGo
    – This gives the SA rights to open this database regardless of issuesAlter Database MyDB Set EMERGENCYGo
    -- Validate the overall database integrity, this can take a whileDBCC Checkdb('MyDB') Go Alter database MyDB SET SINGLE_USER with ROLLBACK IMMEDIATEGo
    -- Force a repair even if you loss data, this can take even longerDBCC Checkdb('MyDB', REPAIR_ALLOW_DATA_LOSS)Go Alter Database MyDB SET MULTI_USERGo Use MyDBGo
    -- Validate the system catalog integrityDBCC 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.

Share This Page