It is recommended that you run DBCC CHECKDB, DBCC NEWALLOC, 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 database consistency check (DBCC) but before the dump, you may want to first dump the database and then execute the DBCC statements 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, these errors 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, 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. Second, if users are actively updating the database while DBCC is running, it might report spurious errors, which can be misleading.
Note Starting with SQL Server 6.5 SP2, DBCC NEWALLOC will not report spurious errors, even if the database is in use. Prior to SQL Server 6.5 SP2, DBCC NEWALLOC is known to be susceptible to spurious errors if the database is in use.
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 nonclustered index pages, which can dramatically reduce the time necessary to execute DBCC statements. It is still advisable, however, to periodically execute full DBCC statements on the database to ensure consistency of the nonclustered indexes as well. The syntax for this option is to include the no_index keyword after the DBCC statement. For example, to perform all the consistency checks on the data pages but skip the checks on any nonclustered indexes, enter the following:
DBCC CHECKDB (pubs, no_index) DBCC CHECKTABLE (inventory, no_index)
You can create Transact-SQL scripts containing DBCCs and use the Database Maintenance Wizard 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 failure 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 earlier mini/mainframe platforms.
Occasionally, when dumping a database, you will receive SQL Server error 3004, stating:
%ld 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, rather than running DBCC CHECKALLOC, run DBCC NEWALLOC on that database, ensuring that no users are currently using the database while DBCC is executing.
Note In SQL Server 6.5, NEWALLOC has replaced the functionality of CHECKALLOC.
Under most circumstances, NEWALLOC will not return any errors, and the previous 3004 error can safely be ignored. If NEWALLOC does report other errors, however, you should contact your primary support provider for further instructions.