SQL: Get the TSQL for Oldest Running Open Transaction DBCC OPENTRAN
Author: jason
Date: 2014-02-07 11:44:23
Category: Musing

The DBCC command OPENTRAN can be used to find the oldest running open uncommitted transaction in a given database. The results contain the SPID, but not the TSQL query. The Start Time is displayed with OPENTRAN. Unfortunately, not the user. DBCC INPUTBUFFER can be used to show what TSQL is being issued, based on the SPID.

This is for the oldest uncommitted transaction, not a general long running process or job...

Here is a clever way to grab the TSQL and Start Time of the Oldest Open Transaction. Note that I put in a non-existent database name MyDB-Typo one Line 1, but with an ISNULL function I go and grab the currently selected database.




The uncommitted TSQL I ran looks like this.




The typical DBCC OPENTRAN results look like this. It runs against the database the query execution window is currently opened on.




Specify a database to check against with it's Name or ID.




Add WITH TABLERESULTS.




Put those results into a Temp Table.




Specify a database, select the SPID and send it to DBCC INPUTBUFFER.




On the big clever query I added ISNULL checks. If there isn't an open/uncommitted transaction the results look like this.




Code

DECLARE @DB_ID INT = (SELECT ISNULL(DB_ID('MyDB-Typo'), DB_ID(DB_NAME())));
DECLARE @OpenTran AS TABLE (Field_Name VARCHAR(255), Field_Value VARCHAR(255));
DECLARE @InputBuffer AS TABLE (EventType VARCHAR(255), Parameters VARCHAR(255), EventInfo VARCHAR(MAX));
INSERT INTO @OpenTran EXEC ('DBCC OPENTRAN (' + @DB_ID + ') WITH TABLERESULTS;');
DECLARE @OldAct_SPID INT = ISNULL((SELECT Field_Value FROM @OpenTran
WHERE Field_Name = 'OLDACT_SPID'),1);
DECLARE @OLDACT_STARTTIME DATETIME = ISNULL((SELECT Field_Value FROM @OpenTran
WHERE Field_Name = 'OLDACT_STARTTIME'),'1/1/1900');
INSERT INTO @InputBuffer EXEC('DBCC INPUTBUFFER(' + @OldAct_SPID + ');');
SELECT DB_NAME(@DB_ID) AS DB, @OldAct_SPID AS SPID, @OLDACT_STARTTIME AS StartTime, EventInfo
FROM @InputBuffer


Code

DECLARE @OpenTran AS TABLE (Field_Name VARCHAR(255), Field_Value VARCHAR(255));
INSERT INTO @OpenTran EXEC('DBCC OPENTRAN (MyTest) WITH TABLERESULTS;');
DECLARE @SPID AS INT = (SELECT Field_Value FROM @OpenTran WHERE Field_Name = 'OLDACT_SPID');
EXEC('DBCC INPUTBUFFER(' + @SPID + ');');



SQL Authority: http://blog.sqlauthority.com/2014/01/19/sql-server-identify-oldest-active-transaction-with-dbcc-opentran/
OPENTRAN: http://msdn.microsoft.com/en-us/library/ms182792.aspx
INPUTBUFFER: http://technet.microsoft.com/en-us/library/ms187730.aspx
DB_ID: http://technet.microsoft.com/en-us/library/ms186274.aspx
DB_NAME: http://technet.microsoft.com/en-us/library/ms189753.aspx




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009