Database consistency checks and transaction log is filing up, help?
Yet anothe forums related question I would like to blog, as I have seen in my experience on newsgroups.
There will be always a question asking about why Transaction log is filling up and blocking is ocurred when a DBREINDEX & CHECKDB processes are under execution. Well the reason is obvious that these DBCC statements are resource intensive and will have impact on system, so the general recommendation is to run them during less traffic hours. Also make a note the Transaction log is heavily used during these operations, so you have to keep up the size in consistent with the storage requirements within your environment. As referred DBCC DBREINDEX is a resource intensive operation and if you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement. So in this case continue the tlog backup schedule to keep up the size, better to resize the transaction log to accomodate the sizes rather than running a shrink operation regularly.
I would like to reiterate kind reference from BOL:
quote:
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting.
If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.
It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.
In addition to this make a note that performing full database backup will break the sequence of transaction log if you have a provision for log shipping on a standby server. It is a good practice to perform intermittent or regular backups for transaction log when the above DBCC operations are under progress. So in this case you must have continue session to perform that backup and restore on destination server too in order to continue the sequence of log shipping.
Few counters on running such an operations on a resource intensive environment:
- If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.
- Be sure that you are not performing other disk I/O operations, such as disk backups.
- Place tempdb on a separate disk system or a fast disk subsystem.
- Avoid running CPU-intensive queries or batch jobs.
- Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.