Handling OPEN Transactions in SQL Server 2008

Dear All,

Its very difficult to automatically track the Long OPEN transactions with all transaction details, In our scenario we have scheduled t-log backup for all databases after 15 mins and we found one of our database t-log backup size grows by more than 2.5 GB daily at 4.00 AM and the LDF file size becomes large.

We have searched for tool which can read the T-log backup to identify the transactions details at that particular time but SQL server 2008 does not allow extracting Transactions from T-logs.

To identify the details of all long running Open transaction which is leading to increase LDF file size, I have searched a lot on internet but didn’t get anything which can fulfill our requirement so done bit of RnD and implemented below solution

The solution is that we have to create a table in master database in which Our Stored procedure will record the transaction details

CREATE TABLE [dbo].[oldtransactions]
[db] [varchar](255) NULL,
[noofmins] [int] NULL,
[changed] [datetime] NULL,
[SPID] [int] NULL,
[command] [varchar](1000) NULL

After this create below Stored Procedure

DECLARE map_cursor CURSOR FOR select name from master..sysdatabases where
not name in (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)
declare @db varchar(200)
OPEN map_cursor
FETCH NEXT FROM map_cursor INTO @db
declare @urenoud int
declare @sspid int
create table #temp(field varchar(50),inhoud varchar(50))
insert into #temp
exec (‘dbcc opentran(‘+@db+’) with tableresults, NO_INFOMSGS ‘)
set @urenoud=0
set @sspid =0
select @urenoud=datediff(n, inhoud, getdate()) from #temp where field=’OLDACT_STARTTIME’
select @sspid=inhoud from #temp where field=’OLDACT_SPID’
drop table #temp
— for inpuitbufer
Declare @Cnt int
Declare @GetSpid int
Declare @Dbname varchar(100)
Declare @EventType nvarchar(100)
Declare @EventInfo nvarchar(1000)
EventType NVARCHAR(100),
Parameters int,
EventInfo NVARCHAR(2000))
Create table #InputBuffer (EventType nvarchar(100) null,
EventInfo nvarchar(1000) null,EventTime DATETIME DEFAULT CURRENT_TIMESTAMP)
if @sspid > 0 and @urenoud > 20 — ( 20 is mins which can be customizable )
Insert into #tempcur
Exec(‘DBCC INPUTBUFFER(‘+@sspid+’)with no_infomsgs’)
Select @EventType = EventType, @EventInfo = EventInfo from #tempcur
Update #InputBuffer set EventType =@EventType,EventInfo =@EventInfo
Truncate table #tempcur
Drop Table #InputBuffer
Drop Table #tempcur
— End Input Buffer section
set @urenoud=isnull(@urenoud, 0)
if @sspid > 0 and @urenoud > 20 — ( 20 is mins which can be customizable )
insert oldtransactions(db, noofmins, changed,spid,command) values (@db, @urenoud, getdate(),@sspid,@EventInfo)
FETCH NEXT FROM map_cursor INTO @db
CLOSE map_cursor
DEALLOCATE map_cursor)

Now create a SQL server Agent Job scheduled to execute above Stored Procedure after each 15 mins (in our case scheduled to run in night only)

In morning to get the details we need to execute below T-SQL and the results are in front of us J

Select [db], [noofmins], [changed], [SPID], [command] from oldtransactions with (nolock)


Prashant Deshpande


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s