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.

Here is an example of a stored procedure that can be used to identify open transactions in SQL Server:

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.