Post reply

Snap Shot Database in Suspect

  • October 18, 2008 at 3:09 pm

    #128071

    Hi All,

    We have one snap shot database and now it is showing suspect.

    Can you please me how to resolve this issue.

    Thanks in advance.

    Raghavender Chavva.

    Thank You.

    Regards,
    Raghavender Chavva

  • October 18, 2008 at 5:02 pm

    #886810

    check the error log and see what happened, but my guess is that you will have to drop the snapshot and recreate it.

  • October 19, 2008 at 2:37 am

    #886853

    Did the drive the snapshot is on or the drive that the source DB is on run out of space?

    There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 20, 2008 at 4:19 am

    #887033

    Whenever a db is in suspect mode then the first thing you need to check the reason as why the db is in suspect mode, and you can easily find it from event viewer & sql error log. If it is some hardware issue or some disk size issue first try to solve that issue as unless otherwise the problem is fixed which caused this, your db wont come online then run this query

    Use Master

    Go

    Sp_resetstatus 'Database Name'

    Restart sql server service & u r done.

    http://deepakrangarajan.blogspot.com/2007/08/how-to-recover-from-suspect-database.html

    http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

    Hope this helps.

    Rohit

  • October 20, 2008 at 4:39 am

    #887046

    Except that you can't repair a snapshot, as it's a read-only partial copy of a DB.

    Books Online

    When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.

    CREATE DATABASE AdventureWorks_dbss1800 ON

    ( NAME = AdventureWorks2008_Data, FILENAME =

    'D:\Develop\Databases\AdventureWorks_data_1800.ss' )

    AS SNAPSHOT OF AdventureWorks;

    GO

    exec Sp_resetstatus 'AdventureWorks_dbss1800'

    /*

    Msg 15658, Level 16, State 1, Procedure sp_resetstatus, Line 44

    Cannot run sp_resetstatus against a database snapshot.

    */

    ALTER DATABASE AdventureWorks_dbss1800 SET EMERGENCY

    /*

    Msg 5093, Level 16, State 1, Line 1

    The operation cannot be performed on a database snapshot.

    Msg 5069, Level 16, State 1, Line 1

    ALTER DATABASE statement failed.

    */

    DBCC CHECKDB ('AdventureWorks_dbss1800', REPAIR_REBUILD)

    /*

    Msg 7921, Level 16, State 1, Line 1

    Repair statement not processed. Database cannot be a snapshot.

    */

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2008 at 4:56 am

    #887511

    Thank you All,

    I ahve recreated the snap sht database.

    Thank You.

    Regards,
    Raghavender Chavva

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply