Monthly Archives: August 2011

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
)ON [PRIMARY]

After this create below Stored Procedure


CREATE PROC CheckOpenTran
as
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
WHILE @@FETCH_STATUS = 0
BEGIN
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)
CREATE TABLE #Tempcur (
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 )
begin
Insert into #tempcur
Exec(‘DBCC INPUTBUFFER(‘+@sspid+’)with no_infomsgs’)
Select @EventType = EventType, @EventInfo = EventInfo from #tempcur
Update #InputBuffer set EventType =@EventType,EventInfo =@EventInfo
end
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
END
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)

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Uncategorized