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.
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.
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019