Regardless of the frequency of the database dumps, it is highly recommended that you always run DBCC CHECKDB, DBCC CHECKALLOC, DBCC TEXTALLOC, and DBCC CHECKCATALOG on a database either just before or just after dumping it, to check the logical and physical consistency of the database. Because transactions can occur during or after the DBCC but before the dump, you may want to first dump the database and then run the DBCCs to ensure that the database was consistent at the time it was dumped. If a database or transaction log that contains errors is dumped, the errors will still exist when the database is reloaded. Under some conditions, this can even prevent successful reloading. There are two important reasons to run DBCC statements on a database when no users are currently using the database. First, if users are actively updating the database while DBCC is running, it can report spurious errors, which can be misleading. Second, because DBCC performs numerous checks on the data pages in the database, it can have a noticeable impact on performance if users are trying to query the database while DBCC is running.
For databases with a large amount of data (for example, in excess of 5 GB), the DBCC statements may take several hours to run, which can hamper the feasibility of running DBCCs on a production server. SQL Server on Windows NT–based computers addresses this by including the no_index option with DBCC statements. When using the no_index option, SQL Server checks only the consistency of the data pages and clustered index pages for each user table, not the non-clustered index pages, which can dramatically reduce the time necessary to run DBCC. It is still advisable, however, to periodically run full DBCCs on the database to ensure consistency of the non-clustered indexes as well. The syntax for this option is to include the no_index keyword after the DBCC statement. For example, DBCC CHECKDB (pubs, no_index) and DBCC CHECKTABLE (inventory, no_index) perform all the consistency checks on the data pages but skip the checks on any nonclustered indexes.
You can create Transact-SQL scripts containing DBCC checks and use SQL Enterprise Manager to schedule these scripts to run during periods of low activity.
Another solution to running DBCCs on large amounts of production data is to have a backup server running. In this configuration, database and transaction logs can be dumped from the production server and loaded onto the backup server. The DBCCs can then be run on the backup server without negatively affecting the performance of the production server. This is also a good safety net in the event that a hardware or other failure causes the production server to become unavailable for a long period of time. In this case, the backup server can quickly become the new production server, resulting in very little disturbance to users. The rapidly declining cost of PC hardware makes this option much more feasible than with the mini/mainframe platforms. (Managing a backup server is discussed in detail later in this appendix.)
Occasionally, when dumping a database you will receive SQL Server error 3004, stating:
%Id uninitialized pages encountered while dumping database <%.*s>. Run DBCC CHECKALLOC on this database for more information, then call technical support if there are any errors.This error occurs if a problem is detected while doing an online dump. The dump procedure takes a snapshot of the database in order to begin the dump. Because some transactions may have allocated pages but not yet initialized them at the time the snapshot was taken, the dump procedure raises this informational message simply as a precaution.
If you encounter this message, the proper procedure, as the error states, is to run DBCC CHECKALLOC on that database, ensuring that no users are currently using the database while DBCC is executing. Under most circumstances, CHECKALLOC will not return any errors, and the previous 3004 error can safely be ignored. If CHECKALLOC does report other errors, however, you should contact your primary support provider for further instructions.
If DBCC CHECKALLOC is run with active users, you may see spurious 2525 or 2541 errors.