How to apply SQL Server DBCC to avoid clogging

Database consistency Detection (DBCC) may be your most important tool in times of crisis. This article gives you a brief introduction to the capabilities of DBCC, which include:

Detect the integrity of tables and related directories.

detects the entire database.

Detects the integrity of a database page.

Rebuilds any directory in the specified table.

Why do you need to learn DBCC?

If you don't even know why you are using DBCC, here are some reasons for this:

Database pages (tables and directories) need to be constantly split, which can break allocations.

The directory may be compromised or inefficient.

The SQL Server engine can sometimes misunderstand your intentions.

Things can be cumbersome when you need a lot of updates (remember that any specified update is actually deleted and inserted).

Individual pages, while still "healthy", may lose their optimal storage footprint.

How to run DBCC

You can run DBCC in two ways: through a command-line window or a Query Analyzer window. If you think it is necessary, you can also determine the time of its operation.

The DBCC command includes the following extensions:

CheckDB: Testing the consistency of the entire database, is the basic method of checking database damage.

CheckTable: Detects problems with specific tables.

Checkalloc: Detects a single page of a database, including tables and directories.

Reindex: Rebuilds a directory for a particular table.

Cachestats: Describes the objects currently stored in the memory cache.

Dropcleanbuffers: Frees all data currently stored in the buffer so that you can continue to detect without using the previous results.

Errorlog: Deletes (shortens) the current log. You can consider determining the time of the operation that contains this command, run it at about one weeks.

FLUSHPROCLNDB: Clears the stored procedure cache for a particular database (using its database ID instead of the name). Use the following code to find the ID:

SELECT dbid from master.dbo.sysdatabases

WHERE name = ' <name your poison>

Indexdefrag: Reduces directory fragmentation but does not lock files so that users can continue to apply the database.

CheckCatalog: Detects consistency between tables and tables in a particular database (which means using foreign keys, and so on). )