Script to find oldest open SQL Server transaction

By:   |   Updated: 2016-11-22   |   Comments (9)   |   Related: More > Scripts

Problem

You have a database that is set to use the SIMPLE recovery model, but nevertheless you notice that the transaction log file continues to grow even though the simple recovery model is supposed to delete the inactive part of the log file when transactions are committed. Clearly there are open transactions that are filling up the log file. You want a simple tool to quickly identify which statements are currently running as the open transactions are filling up the database log file. How can this be accomplished using basic T-SQL?

Solution

The solution involves creating a stored procedure that uses two important DBCC statements: DBCC OPENTRAN and DBCC INPUTBUFFER.

DBCC OPENTRAN helps identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and non-distributed replicated transactions, if any, within the transaction log of the specified database, so that it can be terminated or examined.

DBCC INPUTBUFFER allows us to display the last T-SQL statement that was sent from the client to an instance of SQL Server by using the session ID we can see what statements were last issued.

Stored Procedure to Identify Open Transactions in SQL Server

The procedure runs DBCC OPENTRAN and gets its details and then takes the oldest active session id as the input parameter for DBCC INPUTBUFFER. This gives us the T-SQL statement for the oldest transaction in our database.

Here is the stored procedure. I created in the Northwind database for this demo.

USE Northwind
go

CREATE PROCEDURE [dbo].[GetOldestOpenTransactionCommand]
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @OpenTransactions TABLE (
  ActiveTransaction VARCHAR(25)
  ,Details SQL_VARIANT
  );
 DECLARE @OpenTransactionsCommands TABLE (
  EventType VARCHAR(100)
  ,Params INT
  ,ActiveTransactionText VARCHAR(500)
  );

 -- Execute the command, putting the results in the table.  
 INSERT INTO @OpenTransactions
 EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

 DECLARE c_OpenTransactionsSID CURSOR
 FOR
 SELECT Details AS transSID
 FROM @OpenTransactions
 WHERE upper(rtrim(ltrim(ActiveTransaction))) LIKE '%OLDACT%SPID%';

 DECLARE @tranSID SQL_VARIANT;
 DECLARE @dbcc VARCHAR(100);

 OPEN c_OpenTransactionsSID;

 FETCH NEXT
 FROM c_OpenTransactionsSID
 INTO @tranSID;

 WHILE (@@FETCH_STATUS = 0)
 BEGIN
         PRINT str(cast(@tranSID AS INT))
  SET @dbcc = 'DBCC INPUTBUFFER(' + str(cast(@tranSID AS INT)) + ') WITH NO_INFOMSGS'

  INSERT INTO @OpenTransactionsCommands
  EXEC (@dbcc)

  FETCH NEXT
  FROM c_OpenTransactionsSID
  INTO @tranSID;
 END

 CLOSE c_OpenTransactionsSID;

 DEALLOCATE c_OpenTransactionsSID;

 SELECT ActiveTransactionText
 FROM @OpenTransactionsCommands;

 SET NOCOUNT OFF

 RETURN
END 
GO

Example Use

Let's assume that in the Northwind database someone opens a transaction, but never closed it:

CREATE TABLE X (A INT);
GO
begin tran
  insert into x values (100);

In another session we can execute our new procedure:

use Northwind
go
exec dbo.GetOldestOpenTransactionCommand
go

Here we can see the T-SQL statement and the SPID which is 55 shown on the Messages tab.

Code for Active Transaction in SQL Server

SPID for Active Transaction in SQL Server
Next Steps
  • The procedure was tested with SQL 2012 Developer edition on my personal server.
  • The procedure uses the legacy DBCC OPENTRAN and DBCC INPUTBUFFER commands, so it is compatible with older versions of SQL Server including 2000, 2005, 2008, etc.
  • In the case of several open transactions that fills up the log, you can execute the procedure several times and after each time issue a KILL statement for the oldest transaction found by the procedure. The next procedure execution will show you the next oldest transaction and so on.


Last Updated: 2016-11-22


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, July 19, 2019 - 1:51:37 PM - Herb Back To Top

Here's something interesting.  I tried it again this morning with --NO-- changes to the script JUST to make sure that I would get the error (so I would know it was fixed when implementing your suggestion)... and the error does not appear.  It SEEMS to have fixed itself.

I HATE IT when things work!  ;-)

H


Friday, July 19, 2019 - 9:48:11 AM - Greg Robidoux Back To Top

Hi Herb,

you can try to change the sql_variant to nvarchar(max) in the script to see if that fixes your error message.

-Greg


Thursday, July 18, 2019 - 7:44:19 PM - Herb Back To Top

A quick side note ... even though the script runs perfectly - I see this message in the MESSAGES tab:

Warning: The table '#OpenTransactions' has been created but its maximum row size (8066) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.


Thursday, July 18, 2019 - 6:35:18 PM - Herb Back To Top

Eli leiba and Greg Robidoux - 

Thank you both for the help!

It works great!

It tells me that there IS an open transaction and what the last command was that was executed.

I will pick through the code and see if I can get it to tell me WHO has (and hopefully more info about) the open transaction as well.

Thanks!


Thursday, July 18, 2019 - 3:46:31 AM - Eli leiba Back To Top

Hi

if version is 8.0.2039 (old one) , please try using disk temp tables (the one with #) instead of memory tables


Wednesday, July 17, 2019 - 11:09:53 AM - Greg Robidoux Back To Top

Hi Herb,

try changing from table variables to temp tables.

CREATE PROCEDURE [dbo].[GetOldestOpenTransactionCommand]
AS
BEGIN
 SET NOCOUNT ON
 CREATE TABLE #OpenTransactions (
  ActiveTransaction VARCHAR(25)
  ,Details SQL_VARIANT
  );
 CREATE TABLE #OpenTransactionsCommands (
  EventType VARCHAR(100)
  ,Params INT
  ,ActiveTransactionText VARCHAR(500)
  );
 -- Execute the command, putting the results in the table.  
 INSERT INTO #OpenTransactions
 EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');
 DECLARE c_OpenTransactionsSID CURSOR
 FOR
 SELECT Details AS transSID
 FROM #OpenTransactions
 WHERE upper(rtrim(ltrim(ActiveTransaction))) LIKE '%OLDACT%SPID%';
 DECLARE @tranSID SQL_VARIANT;
 DECLARE @dbcc VARCHAR(100);
 OPEN c_OpenTransactionsSID;
 FETCH NEXT
 FROM c_OpenTransactionsSID
 INTO @tranSID;
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
         PRINT str(cast(@tranSID AS INT))
  SET @dbcc = 'DBCC INPUTBUFFER(' + str(cast(@tranSID AS INT)) + ') WITH NO_INFOMSGS'
  INSERT INTO #OpenTransactionsCommands
  EXEC (@dbcc)
  FETCH NEXT
  FROM c_OpenTransactionsSID
  INTO @tranSID;
 END
 CLOSE c_OpenTransactionsSID;
 DEALLOCATE c_OpenTransactionsSID;
 SELECT ActiveTransactionText
 FROM #OpenTransactionsCommands;
 SET NOCOUNT OFF
 RETURN
END 
GO

Wednesday, July 17, 2019 - 10:26:30 AM - Herb Back To Top

Thanks for writing this all up.

Quick question - 

I'm using : sql server 8.0.2039

Operating system: Microsoft Windows NT 5.2 (3790)

and there are no plans to upgrade this system so using it is a requirement.

---------this item ######################################## 

-- Execute the command, putting the results in the table.  

INSERT INTO @OpenTransactions

EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

----------gives this error

Msg 197, Level 15, State 1, Procedure GetOldestOpenTransactionCommand, Line 18

EXECUTE cannot be used as a source when inserting into a table variable.

------this item ######################################## 

INSERT INTO @OpenTransactionsCommands

EXEC (@dbcc)

-------------gives this error

Msg 197, Level 15, State 1, Procedure GetOldestOpenTransactionCommand, Line 41

EXECUTE cannot be used as a source when inserting into a table variable.


Thursday, December 01, 2016 - 3:43:33 AM - Thomas Franz Back To Top

Nice procedure. The benefit of this procedure is, that it shows me the main statement / first call, while other procedures (as sp_WhoIsActive or the other linked function) shows only the current.

And if you have an "master" procedure that calls x sub procedures which may call other procedures which do some havy write jobs, which would cause triggers to fire, which calls other procedures again... it could be very nice to know the main culprit....


Tuesday, November 22, 2016 - 1:32:01 AM - jeff_yao Back To Top

 Here is probably another concise and rich-in-content solution

 

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools