How to Apply DBCC in SQL Server to avoid blocking

This article explains why we should understand Database Consistency detection (DBCC) and how to run DBCC and apply its five extensions.

Database Consistency detection (DBCC) may be your most important tool in critical moments. This article briefly introduces DBCC functions, including:

◆ Check the integrity of the table and related directories.

◆ Check the entire database.

◆ Check the integrity of the database page.

◆ Recreate the directory in any specified table.

Why do you need to learn DBCC?

If you do not even know why DBCC is used, the following are some reasons:

◆ Database pages (tables and directories) need to be continuously split, which may damage the allocation.

◆ Directories may be damaged or their efficiency may be reduced.

◆ The SQL Server engine sometimes misunderstands your intention.

◆ When a large number of updates are required, it may be difficult (Remember, any specified update is actually deleted or inserted ).

◆ Although a single page is still "healthy", it may lose its optimal storage footprint.

How to run DBCC

You can run DBCC in two ways: through the command line window or Query Analyzer window. If you think it is necessary, you can determine the operation time. (I have never felt necessary to do so, because of all Microsoft products, I am the most confident about the stability of SQL Server. I think it is Raymond's best product. However, it may also cause errors .)

DBCC Commands include the following extensions:

◆ CheckDB: checks the consistency of the entire database and is the basic method for checking database damages.

◆ CheckTable: checks for issues with specific tables.

◆ CheckAlloc: checks a single page of a database, including tables and directories.

◆ Reindex: recreate the directory of a specific table.

◆ CacheStats: indicates the objects currently stored in the memory cache.

◆ DropCleanBuffers: releases all data currently stored in the buffer zone, so that you can continue the detection without using the previous results.

◆ Errorlog: delete (shorten) The current log. You can determine the operation time that contains this command and run it once every week.

◆ FlushProclnDB: clears the Stored Procedure cache of a specific database (uses its database id instead of its name ). Use the following code to find the id:

SELECT dbid FROM master.dbo.sysdatabases WHERE name = '

IndexDefrag: reduces directory split, but does not lock files so that users can continue to apply the database.

CheckCatalog: checks the consistency between a specific database table and a table (the latter means that foreign keys are used .)

1 2 Next Page

[Content navigation]
Page 7: Why do you need to learn about DBCC and run DBCC?	Page 5: Use five extensions