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”

With All Due Respect…

Recently at work, I came back from lunch to my coworkers yelling,

We’ve been looking for you! You need to restore the database!

I was extremely surprised and caught off-guard by the verbal attack. I tried my best to keep my cool, even though my heart was racing fast, and asked for further explanation as to why I should “restore the database?”

They replied, “We can’t connect to our application!Continue reading “With All Due Respect…”

Blogging Baby Steps

Looking back, I don’t remember a time that I ever liked to write. I was the kid in school that always took forever to write a paper because it was “never good enough.”

With some encouragement from my friend Tim Radney, I decided to take the plunge and travel down the road of blogging. I do work full time as a SQL Server DBA so most of my posts will be centered around things I learn everyday at work.

Like the title of this blog says, I’m starting out with “baby steps” but hopefully I’ll be up and running very soon!