Finding Out the Oldest Active Transaction

Following query is used to check the oldest active transactions on a DB. Open Transactions blocks tables and queries will not return any result.

Let’s look at example.

CREATE TABLE [dbo].[emp](

      [empid] [int] IDENTITY(1,1) NOT NULL,

      [empname] [varchar](10) NULL,

      [role] [varchar](10) NULL

) ON [PRIMARY]

Lets begin a transaction…

begin tran

insert into emp

values ('Pranay','MGR')

without firing commit or roll back fire the below query

dbcc opentran

This command will give us the oldest active transaction in DB.