Quick Solve Unclosed Transaction

The quickest way to find a process that transaction is unclosed and what statements it's running is:

1. DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the CURRENT Database of this DBCC OPENTRAN running. You can obtain the Process ID.

2. DBCC INPUTBUFFER(@spid)

After you obtained the Process ID from DBCC OPENTRAN, you can run DBCC INPUTBUFFER to get the last statement sent from the client that the process run by passing in the process ID into it.

Example: We will create a bad stored procedure and run it for demonstration purpose.

/* A Bad Stored Proc. */

USE [TestDB]

GO

CREATE PROC [dbo].[UnclosedTran]

@p1 varchar(50)

AS

BEGIN

BEGIN TRAN

UPDATE Test1 SET col1 = @p1

END

/* Run that SP */

USE TestDB

GO

EXECUTE UnclosedTran 'ABC'

/* Find the SPID of oldest process with transaction unclosed */

/* use sp_MSForEachDB to loop through all databases */

EXEC sp_MSForEachDB 'DBCC OPENTRAN(?)'

/* Get the SPID value from the DBCC OPENTRAN output, then see its input buffer */

DBCC INPUTBUFFER(61) /* replace your spid value */

After you take notes of the “EventInfo” (the bad stored procedure / sql script), then you may decide to KILL the process. E.g.

KILL 61 /* replace your spid value */