Perform DBCC operations on a very large SQL Server database

Original: Perform DBCC operations on very large SQL Server databases

For database maintenance, mainly using DBCC CHECKDB, the following is the use of large databases, small databases are generally used directly:
1, 2008 (2005 I do not confirm) has implemented a snapshot check, that is, when you execute DBCC, The DBMS snapshots a database and then performs a check on the snapshot so that the original library does not affect the lock.
2, using the PHYSICAL_ONLY option, you can check the physical consistency of the database with less overhead. It can also detect torn pages, checksum errors, and common hardware failures that can compromise the user's data security. Therefore, it is recommended to use this option for frequently used production libraries. , you can greatly shorten the time to run DBCC CHECKDB on a large database. The time spent by
3 and CHECKDB depends mainly on:
  A, the size of the database itself,
  B, the current I/O read and write capability, and the busy level
  C, the current system CPU load;
  D, The amount of concurrent modifications to the current database,
  E, the speed at which the tempdb disk is stored,
  F, database object types: such as lobs, will take more time;
  g, checkdb parameters The choice of parameters affects what the DBCC does;
  h, database error types and number of errors;
According to other people's experience: 1T database If there is no error, CHECKDB may take up to 20 hours. If you have hundreds or thousands of databases, even 2 or 300G. It may not run out all day.
now get to the point:
If the database has a partitioned table mechanism, it will be easier to do, and for the partitioned filegroups that store the historical data, it can be set to read-only mode and prevent any error modification because the data is not changed. The DBCC CHECKFILEGROUP can be done once a month or so. For current data, it is best to do DBCC CHECKFILEGROUP alone, two times a week.
If you do not have a very large database of partitions, you can refer to the following:
Monday to Wednesday: Run a set of DBCC CHECKTABLE
Thursday: DBCC checkalloc+ a group of DBCC CHECKTABLE
Friday Saturday: A set of DBCC CHECKTABLE&NBSP is run every day;
Sunday: DBCC CHECKALLOC+DBCC checkcatalog+ a set of DBCC CHECKTABLE.
This method can be attempted for terabytes of databases.

Perform DBCC operations on very large SQL Server databases