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.
|
|
|
|
|
|