What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?

Question:

On our SQL Server 2000 system every Sunday early hours we have a scheduled job to execute the database optimization tasks such as DBCC DBREINDEX and DBCC CHECKDB which is running without any problems. But during this time we are getting errors and alerts about user database transaction log is out of free space. What is the impact on the transaction log for executing the DBCC DBREINDEX and DBCC CHECKDB?

Answer:

As you may be aware DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table and it is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. DBCC DBREINDEX rebuilds indexes dynamically. During this operation it restores the page density levels to the original fillfactor (default); or the user can choose another target value for the page density. Internally, running DBCC DBREINDEX is very similar to using TSQL statements to drop and re-create the indexes manually.

Internally DBREINDEX process occurs as a single, atomic transaction. To reduce the fragmentation, new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data files. At this point all the operation involved in this process is logged by default and attempts to write to the disk whenever CHECKPOINT or BACKUP LOG process is initiated. So to accomodate the changes, SQL Server looks for free space in data and log files and if there is insufficient free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction.

DBCC CHECKDB process performs a physical consistency check on indexed views and validates the integrity of every object in a database by collecting the information, and then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan. This process involves excessive locking and in older versions of SQL Server (6.5 and 7.0) this has negative affect by taking the database essentially offline. In SQL Server 2000 it uses and reads the database transaction log to get a consistent view in order to run the CHECKDB online effectively. The process of involving the  transaction log is read from the LSN of the 'begin tran' log record of the oldest transaction that is active at the time the database scan started, to the LSN at the time the database scan stops. The affect of REDO and UNDO of the transactions are as follows:

• Log records from transactions that commit during that time are used to generate REDO facts. In the scenario of a row insertion record this would produce a REDO fact of 'a row with these index keys was inserted into page A of table B, index C at slot position X'.
• Log records from transactions that rollback or don't commit during that time are used to generate UNDO facts. A row insert record would produce an UNDO fact of 'a row with these index keys was removed from page A of table B, index C at slot position X'

The above REDO and UNDO process will use the log extensively by affecting the volatile changes to available free space. It is recommended to have at least 60% free space available in the transaction log if you are executing the DBREINDEX and CHECKDB statements on larger tables. Also it is a best practice to perform frequent database transaction log backups during this operation in order to keep up the size of database data & log files.