Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL Log Long Running Open Transactions
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-08-22







Solution:




Step 1: Create a new permanent table to log open transactions & related information


CREATE TABLE [dbo].[LOG_OPEN_TRANSACTIONS](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Database] [varchar](100) NULL,
[Longest_Running_Open_Transaction_Sec] [int] NULL,
[Transaction_Log_Size_MB] [int] NULL,
[Total_VLF_Files] [int] NULL,
[SessionID] [int] NULL,
[Open_Transaction_Count] [int] NULL,
[HostName] [varchar](100) NULL,
[Status] [varchar](100) NULL,
[LoginName] [varchar](50) NULL,
[Text] [varchar](250) NULL,
[ProgramName] [varchar](150) NULL,
[InsertedDate] [datetime] NULL,
CONSTRAINT [PK_LOG_OPEN_TRANSACTIONS] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Step 2: Create a new Stoerd Procedure to get & log open transactions & related information


Create Procedure [dbo].[spaLogOpenTransactions]
@ThresholdLongestRunningTransactionInSec int
as

SET NOCOUNT ON;

/*
Exec dbo.spaLogOpenTransactions 60
Select * from [dbo].[LOG_OPEN_TRANSACTIONS] order by LogID desc
*/

Declare @LongestRunningTransactionInSec int;
Declare @SessionID int
Declare @DBName varchar(100)
Create Table #ExcludeListDBs (DB varchar(100))
Insert #ExcludeListDBs(DB) Values ('')
Create Table #ExcludeListLogins (Logins varchar(100))
Insert into #ExcludeListLogins(Logins) Values ('')

Create Table #TLOG(
RecoveryUnitid int
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);

SELECT top 1
@LongestRunningTransactionInSec=DATEDIFF(s, dtat.transaction_begin_time, GETDATE()),
@SessionID=session_id
FROM
sys.dm_tran_active_transactions dtat
INNER JOIN sys.dm_tran_session_transactions dtst
ON dtat.transaction_id = dtst.transaction_id
INNER JOIN Master.dbo.sysprocesses p on dtst.session_id = p.spid
where
db_name(p.[dbid]) not in (Select DB from #ExcludeListDBs)
and
p.loginame not in (Select Logins from #ExcludeListLogins)
order by DATEDIFF(s, dtat.transaction_begin_time, GETDATE()) desc

IF ISNULL(@LongestRunningTransactionInSec,0) > @ThresholdLongestRunningTransactionInSec
Begin

Select @DBName=(db_name(dbid)) from Master.dbo.sysprocesses where spid = @SessionID and db_name(dbid) not in (Select DB from #ExcludeListDBs)

If @DBName is not null and @DBName <> ''
Begin

Declare @SQL nvarchar(1000)
Declare @retval nvarchar(10)
Declare @retvalout nvarchar(10)

Set @SQL =
'
SELECT @retval=convert(decimal(18,1),(size * 8.0)/1024.0)
FROM ' + @DBName + '.sys.database_files
WHERE data_space_id = 0
'

EXEC sp_executesql @SQL, @Params = N'@retval INT OUTPUT', @retval=@retval OUTPUT;

Declare @VLFQuery nvarchar(250)
Declare @VLFCount nvarchar(250)
Set @VLFQuery = 'dbcc loginfo (' + '''' + @dbname + ''') WITH NO_INFOMSGS'
Insert into #TLOG Exec (@VLFQuery)
Select @VLFCount=(Select count(*) as 'Total T-Log VLFs' from #TLOG)

Insert into [dbo].[LOG_OPEN_TRANSACTIONS]
([Database],[Longest_Running_Open_Transaction_Sec],[Transaction_Log Size_MB],[Total_VLF_Files],
[SessionID],[Open_Transaction_Count],[HostName],[Status],[LoginName],[Text],[ProgramName], [InsertedDate]
)
SELECT
@DBName as 'Database',
@LongestRunningTransactionInSec as 'Longest_Running_Open_Transaction_Sec',
@retval as 'Transaction_Log Size_MB',
@VLFCount as 'Total_VLF_Files',
tst.session_id,
tst.open_transaction_count,
p.hostname,
p.status,
p.loginame,
substring(t.text,1,250),
p.[program_name],
getdate()
--, *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) t
inner join Master.dbo.sysprocesses p on tst.session_id = p.spid
Where tst.session_id = @SessionID
--and p.[program_name] not like '%%'
and @DBName not in (Select DB from #ExcludeListDBs)

End
End

Drop Table #TLOG
Drop Table #ExcludeListLogins
Drop Table #ExcludeListDBs

/*
T-SQL Helper:

Select * from Master.dbo.sysprocesses where open_tran <> 0

SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions WHERE [database_id] = DB_ID (N'DBNameHere');

SELECT [log_reuse_wait], [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'DBNameHere';

DBCC OPENTRAN

DBCC LOGINFO

CHECKPOINT

DBCC SHRINKFILE(SALESDB_Log, 100)

SELECT
(size * 8.0)/1024.0 AS size_in_mb
,CASE WHEN max_size = -1 THEN -1 -- Unlimited growth
ELSE (max_size * 8.0)/1024.0
END AS max_size_in_mb
FROM [DBNameHere].sys.database_files
WHERE data_space_id = 0
*/



Images:





That's it !

You can next setup alerting based on numerous field values and thresholds passed over a specified time period.