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).
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)
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:
…but that didn’t bring back anything. So then I ran:
…and it returned an open transaction with its associated SPID.
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.