Problema: Tienes una base de datos que está configurada para utilizar el modelo de recuperación SIMPLE, pero sin embargo, notas que el archivo de registro de transacciones sigue creciendo a pesar de que el modelo de recuperación simple debería eliminar la parte inactiva del archivo de registro cuando se confirman las transacciones. Claramente, hay transacciones abiertas que están llenando el archivo de registro. Deseas una herramienta sencilla para identificar rápidamente qué declaraciones se están ejecutando actualmente mientras las transacciones abiertas llenan el archivo de registro de la base de datos. ¿Cómo se puede lograr esto utilizando T-SQL básico?

Solución: La solución implica crear un procedimiento almacenado que utiliza dos importantes declaraciones DBCC: DBCC OPENTRAN y DBCC INPUTBUFFER. DBCC OPENTRAN ayuda a identificar transacciones activas que pueden estar impidiendo la truncación del registro. DBCC OPENTRAN muestra información sobre la transacción activa más antigua y las transacciones replicadas distribuidas y no distribuidas más antiguas, si las hay, dentro del registro de transacciones de la base de datos especificada, para que puedan ser terminadas o examinadas. DBCC INPUTBUFFER nos permite mostrar la última declaración T-SQL que se envió desde el cliente a una instancia de SQL Server utilizando el ID de sesión, podemos ver qué declaraciones se emitieron por última vez.

Aquí tienes un ejemplo de un procedimiento almacenado que se puede utilizar para identificar transacciones abiertas en 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)
    );

    -- Ejecuta el comando, colocando los resultados en la tabla.
    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

Ejemplo de uso: Supongamos que en la base de datos Northwind alguien abre una transacción, pero nunca la cierra:

CREATE TABLE X (A INT);
GO
BEGIN TRAN
    INSERT INTO X VALUES (100);

En otra sesión, podemos ejecutar nuestro nuevo procedimiento:

USE Northwind
GO
EXEC dbo.GetOldestOpenTransactionCommand
GO

Aquí podemos ver la declaración T-SQL y el SPID que es 55 mostrado en la pestaña de Mensajes.