We're talking hundreds of GBs or 1 TB or more. These databases are now common on SQL Server 2000 and 2005 - at TechEd IT Forum this week (and at SQL Connections the week before) there are many customers with multi-TB databases. Any experienced DBA knows the value of running consistency checks, even when the system is behaving perfectly and the hardware is rock-solid. The two problems that people have with running a full CHECKDB on their VLDB are:
- It takes a long time to run (based on many factors – see my previous post here for details).
- It uses lots of resources – memory, CPU, IO bandwidth, tempdb space.
So it uses lots of resources for a long time. Even with a decent sized maintenance window, the CHECKDB may run over into normal operations. There's also the case of a system that's already pegged in more or more resource dimensions. Whatever the case, there are a number of options:
- Don't run consistency checks
- Run CHECKDB using the WITH PHYSICAL_ONLY option
- Use SQL Server 2005's partitioning feature and devise a consistency checking plan around that
- Figure out your own scheme to divide up the consistency checking work over several days
- Offload the consistency checks to a separate system
No comments:
Post a Comment