Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: Database_suspect

    OK, I am new to sqlserver. Anyway, I was trying to open up one of my db and it had database_suspect next to it and no items listed under it.

    How do I resolve this to bring the db available once again. Thanks.

  2. #2
    Join Date
    Jul 2004
    Try this code:
    sp_resetstatus [ @DBName = ] 'database'

    If you you on BOL and type in Suspect it will outline the steps that you need to take.



  3. #3
    Join Date
    May 2003
    Rochester, NY
    ...I don't think you want to reset the databases status (ti can be dangerous). you should look to restore the database...

  4. #4
    Join Date
    Jan 2003
    i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%

    good luck

    -- Important!!!
    -- There are 3 levels of repair that can be done.
    -- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
    -- then you should stop service and start service again before continue with next step.
    -- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
    -- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
    -- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
    -- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
    -- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.

    -- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
    -- Note: this is name as seen in Enterprice Manager and not physical file name
    dbcc checkdb ('ips',REPAIR_FAST)

    -- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
    dbcc checkdb ('ips',REPAIR_REBUILD)

    -- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
    -- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
    -- To be able to run level 3, database must be in single user mode
    -- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
    -- the SQL service so that the query analyser session you execute it in is the only user.
    sp_dboption 'ips', 'single user', 'TRUE'

    -- Step 3 - replace name the same as step 1
    dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)

    -- If the repair works, then you can set back single user mode with this command
    -- You can also go to database properties in Enterprice Manager
    sp_dboption 'ips', 'single user', 'false'

    -- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
    -- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
    -- the suspect bit automatically.
    exec sp_resetstatus 'ips'

    -- Some more misc scripts that can be useful
    EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
    EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!

    -- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.
    sp_dropuser 'ips'

    EXEC sp_attach_db @dbname = N'inbound_mike',
    @filename1 = N'd:\mssql7\data\inbound_Data_mike.mdf',
    @filename2 = N'd:\mssql7\data\inbound_Log_mike.ldf'

    EXEC sp_attach_db @dbname = N'SODUAT',
    @filename1 = N'e:\mssql7\data\SODUAT_Data.mdf',
    @filename2 = N'e:\mssql7\data\SODUAT_Log.ldf'

    RESTORE DATABASE ips FROM DISK = 'd:\ipsdatabasedump.bak' -- must drop rep. regardless
    WITH MOVE 'ips' TO 'g:\mssql7\data\ips.mdf',
    MOVE 'ips_log' TO 'c:\mssql7\data\ips_log.ldf'

    ALTER DATABASE Tempdb Modify file
    ( NAME = 'tempdev', FILENAME = 'e:\mssql7\data\ips_.mdf')

    ALTER DATABASE Tempdb Modify file
    ( NAME = 'templog', FILENAME = 'h:\mssql7\data\tempdb.ldf')

    exec xp_cmdshell 'net start "pcANYWHERE Host Service"'
    exec xp_cmdshell 'net stop "pcANYWHERE Host Service"'
    exec xp_cmdshell 'dir d:\mssql7\backup'
    exec xp_cmdshell 'dir c:\mssql7\data'
    exec xp_cmdshell 'del c:\mssql7\devtools\samples\backup\*.* /q' -- q = quiet, don't ask for confirmation when *.*

Posting Permissions

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