장기 트랜잭션 확인 및 경고 설정

장기 트랜잭션 확인 및 경고 설정

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

장기 트랜잭션이 많은 경우 잠재적인 문제(차단/ 잠금)가 발생 할 수 있다. 장기 트랜잭션이 발생하는 경우는 실제 작업이 오래 걸리는 경우도 있지만 사용자가 트랜잭션을 닫지 않아 발생하는 경우도 많다.

 

따라서 장기 트랜잭션이 있는지 확인하여 알림을 받을 수 있다면 DB를 운영하는데 많은 도움이 될 수 있다.

 

다음 스크립트는 현재 실행 중인 트랜잭션의 목록을 나타낸다.

SELECT * FROM sys.dm_tran_active_transactions dtat     INNER JOIN sys.dm_tran_session_transactions dtst ON dtat.transaction_id = dtst.transaction_id;

 

 

 

위의 쿼리를 활용하여 장기 트랜잭션이 운영자가 설정한 시간 이상 지속되고 있을 때 간단히 메일 또는 문자서비스로 알림을 받을 수 있다.

 

다음 스크립트는 10분 이상 실행되고 있는 장기 트랜잭션이 발견될 경우 sqlmail을 사용하여 운영자에게 알림을 전달하는 스크립트이다.

/* NOTE: You have to configure/set the following 3 variables */ DECLARE @AlertingThresholdMinutes int = 10; DECLARE @MailProfileToSendVia sysname = 'General'; DECLARE @OperatorName sysname = 'Alerts';   ------------------------------------------------------------- SET NOCOUNT ON;   DECLARE @LongestRunningTransaction int; SELECT @LongestRunningTransaction = MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE())) FROM sys.dm_tran_active_transactions dtat INNER JOIN sys.dm_tran_session_transactions dtst ON dtat.transaction_id = dtst.transaction_id;   IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN   DECLARE @Warning nvarchar(800); DECLARE @Subject nvarchar(100);   SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME; SET @Warning = 'Check SSMS > Server > Reports > Top Transactions By Age.';   EXEC msdb..sp_notify_operator @profile_name = @MailProfileToSendVia, @name = @OperatorName, @subject = @subject, @body = @warning; END

 

Sqlmail을 사용하기 위해서는 sqlmail 설정이 필요하다.

 

 

[참고자료]

http://sqlmag.com/blog/setting-alerts-long-running-transactions

 

 

2013-11-29 / 강성욱 / http://sqlmvp.kr