Silverfox (3/11/2014)
however doesnt using tablock, switch to using tempdb for the work that is carried out.
No. It switches to using table locks for stability rather than a database snapshot.
But i totally agree with what you are saying, does the suspect_pages table in msdb, indicate data corruption, or are they purely read attempts that failed and have got the pages marked as suspect.
In this case, they're error 824, meaning the read request succeeded but the page returned to SQL by the OS was invalid. Failed checksum or other damage. So either the page has been altered on disk after SQL wrote it out, or the read operations are mangling the data in flight.
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