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

    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
    Posts
    191
    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.

    Thanks

    Lystra

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ...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
    Location
    Vietnam
    Posts
    188
    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 *.*
    */
    qha_vn

Posting Permissions

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