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.

SQL Server Database Instance-Level Backup Compression Setup

I was fortunate enough to attend Paul Randal’s and Kimberly Tripp’s IETPO1 this past Spring. During the week long training I met Tim Radney (he’s a SQL Consultant at SQLSkills). I approached him, introduced myself and as we were talking, the subject of SQL Server backups came up. I explained my work’s current backup strategy and how I’d like to make it more efficient, both in speed and disk space. Tim suggested I enable the instance-wide backup compression option in SQL Server Management Studio (see image below) Continue reading “SQL Server Database Instance-Level Backup Compression Setup”