When does a database get marked as suspect?

  • Theoretical question thankfully 😀

    I know it can be marked as suspect on startup due to damaged or missing data or log files, and also if the number of suspect pages passes a certain threshold.

    Are there any other times or reasons that a database could be marked as suspect?

    Thanks!

  • Two things:

    SQL encounters a corrupt database page or log record when doing a rollback of a transaction

    SQL encounters a corrupt database page or log record during crash recovery (when opening a database)

    Missing files result in the recovery_pending state.

    Lots of pages in the suspect_pages table just results in lots of pages in the suspect_pages table.

    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
  • I had an example only this morning. A RAID array failed that contained some database files and the databases were marked as suspect.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • GilaMonster (8/13/2012)


    Two things:

    SQL encounters a corrupt database page or log record when doing a rollback of a transaction

    SQL encounters a corrupt database page or log record during crash recovery (when opening a database)

    Missing files result in the recovery_pending state.

    Lots of pages in the suspect_pages table just results in lots of pages in the suspect_pages table.

    Thanks - my memory is obviously a little rusty!

    I mentioned missing files too as dbpropertyx(db,status) returned 'suspect' & querying sys.databases returned 'recovery_pending' when I tried starting a database without the log file.

  • DatabasePropertyEx is old, it's SQL 2000 or earlier and in SQL 2000 there was only the suspect state. Recovery_pending was added in SQL 2005. I much prefer using sys.databases for the state, it's more specific.

    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 5 posts - 1 through 4 (of 4 total)

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