Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Johannesburg, South Africa
    Posts
    28

    Question Unanswered: MSDB Database SUSPECT - RESET PROCEDURE ?

    I have a regional server running SQL2000 and Merge replication, the MSDB, NORTHWIND and 2 User DB's have been marked suspect. I cannot get into query analyzer etc to run the necessary scripts as it is all suspect.

    Any ideas on how I can reset the status on the MSDB database??
    Unfortunately there is no backup of either of the 2 system databases

    ** ANY ASSISTANCE WOULD BE GREATLY APPRECIATED **

    J

  2. #2
    Join Date
    Jun 2003
    Posts
    31
    Reset database with "suspect" status:

    Do the following steps from the Query Analyzer:

    1) Use Master
    GO
    EXEC sp_resetstatus <dbname>
    2) Stop & Start the SQL Server
    3) Run DBCC CHECKDB
    4) Do the Full database backup (Master DB included)

    If the above steps didn't resolve the problem, run the query below:

    Use Master
    GO
    EXEC sp_configure 'allow updates', 1
    GO
    Reconfigure with override
    GO

    Do the step 1 ~ 4 above, then:

    Use Master
    GO
    EXEC sp_configure 'allow updates', 0 -- to turn off
    GO
    Reconfigure with override
    GO

    NOTE: If the database become "suspect" because it ran out of disk space, you have to do the step #1 above, then use the "ALTER DATABASE ..." to add more data file (or log file), Stop & start the SQL Server and finish with step #3 & #4 as above.

    Good luck.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Valid reponse by SVT and make sure to have regular backups for SYSTEM Databases too in future.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •