Hi all,
You can run physical only, which does some checks, but not all of it.
yes, and we do that on the production server.
The only solution is to really get a good second box and throw some horsepower on it. I'd try to run it every week.
yes, agree, and we have done that. Only problem is that a restore (from disk not from tape) takes 3 days, and DBCC CHECK_DB another 2 days.
That leaves very little time for other use of the box.
My manager thinks that's a little expensive.:-)
So we restore about once a month, and do the check_db most of the times.
The other thing which is a "cheap" check, but is semi-safe for me, is to run a SELECT * from all tables and if they all complete, at least you have all your data accessible.
That would only check the clustered index, and it will also cost a lot of DiskIO and CPU. Not sure if it will upset the cache too much.
@andrew: Thank you for the links for SQLSkills. Here they are a well-known high quality source of information. I've almost finished watching the 40+ hours of video. It has taken two years because I only have an hour or so every week. But time well-spent.
@TravisDBA: Thank you for the link. It is futher spelled out in the videos. But nice and crisp link.
@michael-2 Valentine Jones:
sends an email to DBAs if it finds an error
How do you check that you get the email?
I mean; are you sure that you receive all emails?
I do IO testing with SQLIO
We did that too, and it took a long time for the SAN Vendor (HP) to realise that they were not delivering the promised IO with their recommended setup.
"Thin Provisioning" I think they called it. We currently have 9 GB/s. It sure helps on a busy system.
We too use Ola Hallengrens stored procedure "DatabaseIntegrityCheck" at [/url]
It is better than anything else I've seen so far.
Best regards,
Henrik Staun Poulsen
Stovi Software