To work around this problem, you can delete the corrupted object according to DBCC CHECKDB suggestions. The DBCC CHECKDB command examines the allocation and the structural integrity of all the objects in the specified database. For example, use the following example to run the DBCC CHECKDB command for the current database and the pubs
-- Examine the current database.
-- Examine the pubs database without nonclustered indexes.
DBCC CHECKDB ('pubs', NOINDEX)
The DBCC CHECKDB command is a CPU-intensive and disk-intensive operation. Each data page that requires checking must first be read from the disk into memory. Additionally, the DBCC CHECKDB command uses the tempdb
database to sort. If you are performing transactions while the DBCC CHECKDB command is running, the transaction log continues to grow because the DBCC CHECKDB command blocks log truncation until the command is finished reading the log. We recommend that you run the DBCC CHECKDB command when the load on the server is light. If you run the DBCC CHECKDB command during heavy use, expect a performance hit on the transaction throughput and on the DBCC CHECKDB completion time.
To improve the performance of the DBCC CHECKDB command, use the following suggestions:
- Run CHECKDB when the system usage is low.
- Make sure that you are not performing other disk I/O operations, such as disk backups.
- Put the tempdb database on a separate disk system or on a fast disk subsystem.
- Provide sufficient room for the tempdb database to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space is required for the tempdb database.
- Avoid running CPU-intensive queries or batch jobs.
- Reduce active transactions while the DBCC command is running.
- Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.
If you use CHECKDB with the system event log, you may see that a hardware-related event is causing the inconsistency. You can use the DBCC CHECKDB command with the PHYSICAL_ONLY option to examine the physical structure of the page and the record headers. This operation performs a quick check to see if hardware-induced errors are suspected.