August 13, 2012 at 10:16 am
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!
August 13, 2012 at 10:43 am
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
August 13, 2012 at 11:10 am
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" 😉
August 14, 2012 at 7:20 am
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.
August 14, 2012 at 7:43 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply