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:


…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.

Setup SQL Email Alert for Disk Space Usage

I had an application go kaput on me all of a sudden and that wasn’t good. I had gotten back from lunch (always happens when I get back from lunch) and was immediately approached by the Sys Admin saying that a certain web application couldn’t connect to the SQL database. He wanted me to check out why and get back to him ASAP.

So I quickly log into the database server and see that SQL server is running fine, the SQL account that the web application uses is not “locked” and the password expiration is not checked (meaning that technically the password for that SQL account should never expire). I look at the disk drives by clicking on “My Computer” in Windows Explorer and quickly saw …

“E: Drive 0 GB free of 99 GB”

We have SQL Server configured to store Audit files on the “E” drive. I went into the folder and removed the oldest month worth of audit files. That freed up about 20 GB of space.

Roughly 5 minutes later the same Sys Admin ran in my office saying, “hey! I reset the SQL Account password and it works now.”


I know. What a coincidence right? :)

I believe the reason why the application couldn’t connect to the database is because SQL Server couldn’t write any more audit files to the disk drive. As a result, SQL Server refused outside connections.

I wrote a script that checks whether a specific disk drive space goes below a given threshold. If so, it will send the DBAs an email alert.

Here is the script. Feel free to use/modify it however you see fit.

--create temp table for results
create table #freespace
(drive char(1),mb_free int)

--insert drive data into temp table
insert into #freespace exec sys.xp_fixeddrives

declare @subject varchar(100)
declare @profile_name varchar(25)
declare @body varchar(200)
declare @gb_free int
declare @recipients varchar(50)

--you can specify whatever drive leter
select @gb_free = (mb_free / 1024) from #freespace where drive = 'E'

--you can specify whatever number you want. I put 20GB.
if (@gb_free < 20)
  SET @profile_name = 'Profile Name goes here'
  SET @recipients = ''
  SET @subject = 'ALERT!! E: drive is BELOW 20 GB'
  SET @body = 'Please check the E: drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(varchar,@gb_free) + ' GB of free space left on the E drive.'
  exec msdb.dbo.sp_send_dbmail
  @profile_name = @profile_name,
  @body = @body,
  @subject = @subject,
  @recipients = @recipients
--drop temp table
drop table #freespace;

SQL Server Email Alerts Setup

Recently at work we had an issue where a SQL login account’s password expired and the application that uses that login stopped working. The Window’s password policy for our organization requires passwords to expire after 60 days.

Management requested that we get “an email when the password is about to expire so that we can change it to prevent the application from not being able to connect.”

So I created a little script that runs daily (via a SQL Job) to check all SQL Server login accounts for passwords that will expire in ’10’ (you can change the number to whatever you want) days or less. If any results are found, it will generate and send an email with the SQL login as a reminder that you have “X” number of days until the password expires.

Below is the script. Feel free to use/modify it for your specific needs.

USE master;

DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
DECLARE @SQLAccountUser as varchar(25)
DECLARE @DaysToExpire as varchar(10) = '10'   -- you can pass this number as a parameter when calling this SP.
--Declare Cursor and get all SQL Accounts that have 'Password Expiration' enabled.
select name 
from sys.sql_logins
where is_disabled = 0 and is_expiration_checked = 1 and (
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) > 0 and
(SELECT CAST((SELECT LOGINPROPERTY(name, 'DaysUntilExpiration')) AS VARCHAR)) <= @DaysToExpire )
--Open the cursor
OPEN get_SQLAccount;
FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser; 
    SET @p_profile_name = N'Your Profile Name'
    SET @p_recipients = N''
  SET @p_subject = N'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  SET @p_body = 'SQL Account Password Set to Expire for ' + @SQLAccountUser + ' in ' + @DaysToExpire + ' days.'
  EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @p_profile_name,
     @recipients = @p_recipients,
     @body = @p_body,
     @body_format = 'HTML',
     @subject = @p_subject
  FETCH NEXT FROM get_SQLAccount INTO @SQLAccountUser;
CLOSE get_SQLAccount;