MSSQL DBCC How good are they really ?

#sqlserver #database #tutorial #sql

What does DBCC mean ? Well, it stands for Database Console Commands, of course.
When you're working with SQL Server, these commands are like the secret weapons for any DBA. They're super powerful tools for troubleshooting, monitoring, and ensuring your data stays in check and safe.

But how effective are these commands really ? Are they as good as they seem ?
Let's dive into some of the most useful DBCC commands, how they work, and when you might want to use them.

To explore all commands (documented and undocumented) we first need to enable the trace flag for detailed output, then use the command to view them.

DBCC TRACEON(2588) WITH NO_INFOMSGS;
DBCC HELP ('?') WITH NO_INFOMSGS;

No, if you're not sure how to use some of those commands like for example DBCC CHECKDB, you can simply run it like this,

DBCC HELP ('CHECKTABLE') WITH NO_INFOMSGS;

There's an extensive list of DBCC commands, each serving a specific purpose.
This will give you a complete breakdown of how to use the command and its various options.

Bellow are some of the most common ones, with real practical scenarios and steps how to use them ..

DBCC CHECKDB

This is the go-to command for verifying the physical and logical integrity of an entire database. It's typically executed when corruption is suspected, or as part of regular health checks. Create an SQL job to execute this daily or weekly depending on your database size.

When corruption is detected, especially if multiple objects are affected then this command often becomes your first line of defense.

Before attempting any repair, isolate the database to prevent active connections from interfering with the process.

ALTER DATABASE [AbeDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

This ensures only privileged users can access the database while SQL Server rolls back all active transactions.

You can now rerun DBCC CHECKDB with a repair parameter.

DBCC CHECKDB ('AbeDB', REPAIR_FAST); --minimal repair
DBCC CHECKDB ('AbeDB', REPAIR_REBUILD); --rebuild bad index
DBCC CHECKDB ('AbeDB', REPAIR_ALLOW_DATA_LOSS); --can result dataloss

REPAIR_ALLOW_DATA_LOSS option should never be your first choice in production. If REPAIR_FAST fails, restoring from a clean backup is almost always the safer option.

Avoid data loss unless absolutely necessary. Ensure consistent and verified log backups.
Also do not use command DBCC REPAIRDB. This is an older and not recommended command that has been replaced by CHECKDB (Since SQL Server 2005).

DBCC CHECKALLOC

This command is especially useful when you suspect on storage level or allocation related corruption.

DBCC CHECKALLOC ('AbeDB'); --check
DBCC CHECKALLOC ('AbeDB', FIX) WITH NO_INFOMSGS; --repair

DBCC CHECKTABLE & CHECKINDEX

Another good option if you don't want to scan the whole database and want to focus on specic table or index, or you already know where the problem.. then use this commands with WITH EXTENDED_LOGICAL_CHECK.
It will include better logical integrity checks and check index consistency, foreign keys, null and unique constraints.

Go by logic "Start broad, then narrow down".

DBCC CHECKTABLE ('AbeTable') WITH EXTENDED_LOGICAL_CHECK
DBCC CHECKTABLE ('AbeTable', REPAIR_REBUILD); --repair
DBCC CHECKTABLE ('AbeTable', REPAIR_ALLOW_DATA_LOSS); --data loss

DBCC CHECKINDEX ('AbeTable', 1) --check index
ALTER INDEX IndexName ON AbeTable REBUILD; --rebuild it

DBCC OPENTRAN

Used to display information about the oldest active transaction within a specified database. With this helpful command you can find blocking processes that need to be terminated to free up resources. Used when troubleshooting why the transaction log is not shrinking or is growing uncontrollably, as open transactions can prevent log truncation during backup.

DBCC OPENTRAN('AbeDB');

DBCC FREEPROCCACHE & DBCC FREESYSTEMCACHE

FREEPROCCACHE is used if you suspect that the cache contains bad or suboptimal execution plans. You can clear specific cached plan.
When a query is executed, sql server reuses an existing execution plan from the cache if it exists, which can improve performance because it avoids the need to recompile the query each time.

DBCC FREEPROCCACHE (0x05004320D5B7S12701B91BBE5B5082B6) WITH NO_INFOMSGS;;

FREESYSTEMCACHE allows you to clear various caches based on the context you choose. The PLANCACHE option is less impactful than FREEPROCCACHE because it only clears the execution plans, without affecting other system caches such as the buffer pool.

DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESYSTEMCACHE ('BUFFERPOOL'); --pages of data read from disk
DBCC FREESYSTEMCACHE ('PLANCACHE'); --recompile queries with the new index
DBCC FREESYSTEMCACHE ('COLUMNSTORE');--columnstore indexes

DBCC TRACEON & TRACEOFF & TRACESTATUS

For deeper analysis, these commands allow you to enable, disable, and inspect SQL Server trace flags.

Trace flags are commonly used for debugging, performance troubleshooting, and internal behavior analysis. They can be enabled at the session level or globally, and you can even configure them at server startup if permanent behavior changes are required.
They are primarily used to either change the behavior of the SQL Server engine or to log additional details in the SQL Server logs.
Be cautious not to overwhelm your server with excessive logging. That's why it's important to disable trace flags after you've completed your analysis.

Bellow is a few examples and most commont traces you should use.

Trace 1222: Gives detailed information about deadlock into SQL Server error log..

DBCC TRACEON(1222); --enable flag
DBCC TRACEON(1222,-1); --enable globaly
DBCC TRACEON(1204, 1222); --enable multiple flags
DBCC TRACESTATUS(1222); --check active flags
DBCC TRACEOFF(1222); --turn off

To ensure your configuration persists after every server restart, you need to add the trace flag at the server level,

Open SQL Server Configuration Manager,
Right click on SQL Server Services,
Select Properties, go to the Startup Parameters tab,
Add a new line with the trace flag: -T1222.

Here are some other useful Trace Flags,

1222 - log deadlock in server logs
1204 - locking info with quick analysis
3226 - stops successful database backups from logging
9567 - compression for fast AG Automatic seeding
3042 - disable pre allocation of space during backup
3604 - send output to client
1117 - autogrow all files together ..... And many more.

So, how good are they really ?

While DBCC commands can catch many types of corruption, they aren't a one-stop solution. They may not catch all possible issues, and some might require deeper troubleshooting.
The answer lies in your knowledge of commands and the specific needs of your database.
The best way to truly understand the power of DBCC is to experiment with different commands in your own test environment.
With the right knowledge and caution, DBCC commands can be incredibly effective tools in your DBA arsenal.

Let's have a look at those arsenal again 🤯🤯

dbcc sqlperf
dbcc inputbuffer
dbcc checkdb 
dbcc memorystatus
dbcc freeproccache
dbcc useroption
dbcc freesystemcache
dbcc show_statistics
dbcc dropcleanbuffers
dbcc showcontif
dbcc opentran
dbcc addtempdb 
dbcc checkalloc 
dbcc checkcatalog 
dbcc checkindex 
dbcc checkstorage
dbcc checktable
dbcc checkverify 
dbcc complete_xact
dbcc engine
dbcc fix_texT
dbcc forget_xact
dbcc indexalloc
dbcc monitor
dbcc pravailabletempdbs
dbcc reindex
dbcc serverlimits
dbcc stackused
dbcc tablealloc
dbcc zapdefraginfo 
dbcc quorum
.....
...
..

References: