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. Continue reading “How to use DBCC HELP”
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.
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.