May 7, 2009 at 6:01 am
Hi everyone,
I'm having trouble with running integrity checks on a database that has a few very large tables.
The problem is the database is heavily active 24/7, I normally carry out an integrity check before a full backup, but the integrity checks causes the users to timeout.
I've tried running check table on the large tables but that causes the same issue.
Is anyone aware of an alternative solution to running integrity checks on very large tables?
SQL 2005 64 bit standard edition being used
Any help/advice will be very much appreciated.
May 7, 2009 at 8:31 am
How often are you running CheckDB at the moment?
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
May 7, 2009 at 9:06 am
I'm not running it at all now, it was scheduled for once a week, but database increased rapidly to new functionality, so have only managed to run it once during a scheduled patch update, otherwise unable to schedule it regularly as the application is 24/7.
I'm thinking the only option is eneterprise edition so can use the snapshot feature and run the checks agaist that, but this will require extra disks and licence costs.
May 7, 2009 at 9:18 am
Shiraz (5/7/2009)
I'm not running it at all now, it was scheduled for once a week, but database increased rapidly to new functionality, so have only managed to run it once during a scheduled patch update, otherwise unable to schedule it regularly as the application is 24/7.I'm thinking the only option is eneterprise edition so can use the snapshot feature and run the checks agaist that, but this will require extra disks and licence costs.
Ask management which they'd rather have occur - spend more money or have a 24x7 database down at some point because you couldn't run a consistency check and now have a corrupt database?? Or, is there anyway you can get management to allow you force a scheduled outage on a regular basis for maintenance (maybe like Saturday night, 12:00AM)? One way or another, it's critical to know the health of the database.
-- You can't be late until you show up.
May 7, 2009 at 9:29 am
CheckDB uses a database snapshot anyway. Running it against a snapshot is not going to make any difference. It doesn't take any locks as it works (and never has), and the timeouts are probably just the result of the additional load that it puts on the system. Paul's blog post give the best options for CheckDB on a large system, and he should know, seeing as he wrote that feature.
You need to be running checkDB. If corruption occurs, the sooner it's picked up, the more options there are to fix it. Leave it for a long time and you often need to discard data or have a large amount of downtime to fix the problem
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
May 11, 2009 at 5:09 am
Thanks guys, specially GilaMonster for the link. 🙂
We've gone with the restoring to a reporting server and carrying out Integrity checks on there as the only available solution for us currently.
May 11, 2009 at 7:01 am
Shiraz (5/11/2009)
We've gone with the restoring to a reporting server and carrying out Integrity checks on there as the only available solution for us currently.
As long as it's being done....
-- You can't be late until you show up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply