distribution db became suspect while replication

  • Image IT

    SSC Veteran

    Points: 277

    HI,

    We are running a transactional replication between 2 servers (push subscriber). both the distribution and subscriber DB rely on the same server.

    for some reason the distribution DB became "Suspect" and cannot be reached, stoping the subscriber from being update.

    What could be the reasons of the situation?

    The Distribution server, is under low-level usage (few users use it),

    we did not find anything weird in the sql server error logs, or the windows error log.

    WE didnt find any HW failure, or corrupted disk file.

    Any help…??

    Thnks,

    YK


    ::: Image IT ::::
    you cant allways get what you want
    but you try so hard....

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Look in the SQL error log. There will be messages detailing why SQL marked the DB suspect.

    Most common - data corruption caused by IO subsystem problems.

    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
  • Image IT

    SSC Veteran

    Points: 277

    Thanks Gail,

    I have more questions:

    Which I/O subsystems you mean? (our server or the server holds the distribution & subscriber DB)

    Can it be because of the replication configuration?

    Because its a replicated sever, what u think will be easiest:

    1. to try and recover the suspect DB

    2. to reinitialize the replication all over again (off course after try and find out the reason of the "suspect" situation)?

    Thanks in advance.

    YK


    ::: Image IT ::::
    you cant allways get what you want
    but you try so hard....

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Image IT (3/20/2011)


    Which I/O subsystems you mean?

    The one that the suspect database is stored on.

    Can it be because of the replication configuration?

    Highly unlikely.

    Suspect means SQL encountered data or log file damage during crash recovery or during a rollback

    Because its a replicated sever, what u think will be easiest:

    1. to try and recover the suspect DB

    2. to reinitialize the replication all over again (off course after try and find out the reason of the "suspect" situation)?

    Without seeing the messages in the error log that explained why SQL marked the DB as suspect I would rather not theorise on an optimal recovery.

    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
  • Image IT

    SSC Veteran

    Points: 277

    Thanks Gail,

    We will look deeper in the error logs,

    and get back to you 🙂

    Thnks Again.

    YK


    ::: Image IT ::::
    you cant allways get what you want
    but you try so hard....

  • zavhrye

    SSC Veteran

    Points: 216

    exact same thing happened to me, I'm having problems turning the db back online and we have NO BACKUP. Tried using DBCC CHECKDB (Reporting,REPAIR_ALLOW_DATA_LOSS) but it failed and encountered this error:

    "DBCC CHECKDB

    Msg 8930, Level 16, State 11, Line 1

    Database error: Database 12 has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup.

    Msg 8921, Level 16, State 1, Line 1

    Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors."

    This is because of the db being a subscriber any intelligent answer would be appreciated. Thanks.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    zavhrye (5/17/2012)


    This is because of the db being a subscriber any intelligent answer would be appreciated. Thanks.

    No, it's not because the database is a subscriber, it's because your database is fatally corrupt. Restoring a backup or reinitialising the subscriber is your only option here.

    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

Viewing 7 posts - 1 through 7 (of 7 total)

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