How to use DBCC HELP

Memorizing DBCC commands (or any command for that matter) can be difficult if you don’t use it often. Thank goodness for the folks at Microsoft for creating the DBCC HELP command. Below is a quick way to use DBCC HELP to figure out any command (with syntax) that might have slipped your mind.

First, run DBCC HELP(‘?’) to display a list of available DBCC commands (see image below).

DBCC HELP RESULTS

Second, replace the ‘?’ with whatever DBCC command you want help on. For example, let’s use “checkdb.” Once you run DBCC HELP(checkdb) you get a printout of the CHECKDB syntax. (see image below)

DBCC HELP CHECKDB RESULTS

Find Rogue Transactions in SQL Server

One of the developers approached me today asking why their simple SELECT SQL query was taking forever. I walked over to their desk and noticed their SQL code had a BEGIN TRAN but no COMMIT or ROLLBACK. I ran a:

SELECT @@trancount

…but that didn’t bring back anything. So then I ran:

DBCC OPENTRAN

…and it returned anĀ open transaction with itsĀ associated SPID.

How to find rogue transactions in SQL Server

I used the KILL command to kill SPID 57 (Kill 57) and the developer’s query returned instantly.

And just in case you were wondering, the cause of the rogue transaction was a BEGIN statement that the developer ran without a COMMIT or ROLLBACK and the developer tried to access that same table in another session window.