INF: How to Determine Tempdb Size Required by DBCC CHECKDB

• Microsoft SQL Server versions 6.5, 7.0

SUMMARY

Microsoft SQL Server version 7.0 may require a larger tempdb than earlier versions when running DBCC CHECKDB. The stored procedure sp_checkdbtempsize predicts the approximate tempdb size required.

MORE INFORMATION

In SQL Server 7.0, DBCC CHECKDB and CHECKALLOC are much faster than earlier versions. This performance increase occurs partly because SQL Server 7.0 uses tempdb as an interim storage area while the command runs. Versions of CHECKDB and CHECKALLOC earlier than 7.0 do not use tempdb.

In versions earlier than 7.0, tempdb size is determined by the sum of all concurrent temporary storage requirements for regular queries, not for utility operations like DBCC. See SQL Server Books Online for more information about tempdb.

Starting with version 7.0, the required tempdb size may be larger than in previous versions because DBCC uses tempdb. This should not be a problem if tempdb is set to automatically grow (autogrow) and there is sufficient disk space. Tempdb defaults to autogrow, and this is the recommended setting. For more information, open SQL Server Books Online and search for the phrase "Using Files and Filegroups to Manage Database Growth" (include the quotation marks).

Knowing the approximate amount of tempdb space required by DBCC CHECKDB is useful when checking large databases. Having that information allows you ensure that sufficient space is available for the DBCC statement to complete.

SQL Server 7.0 Setup installs the sp_checkdbtempsize stored procedure which provides a rough estimate of the amount of tempdb storage needed for DBCC CHECKDB in a given database. This stored procedure is located in the master database, but is not documented in SQL Server Books Online.

Note that the space prediction is only an approximation which may vary in accuracy; you should allow enough space for tempdb to grow to a size somewhat larger than the predicted value.

To use the stored procedure, change to the intended database and then run the procedure. For example from Query Analyzer or the osql utility, execute the following:

   use master
   go
   sp_checkdbtempsize
   go 

TOTAL: 941 KB

Additional query words: prodsql

Keywords : SSrvAdmin SSrvStProc SSrvTran_SQL
Version : WINNT:6.5,7.0
Platform : winnt
Issue type : kbinfo