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

 

  • 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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38533
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20771
1894 SQL Server에서 Drop 및 Delete 사용자 찾기 jevida(강성욱) 2016.10.12 1701
1893 SQL Server 트랜잭션 로그 읽기 jevida(강성욱) 2016.10.12 2284
1892 MaxBCPThreads에 따른 BCP 병렬출력 jevida(강성욱) 2016.10.11 1124
1891 확장이벤트와 dm_os_wait_stats 대기유형 매핑 jevida(강성욱) 2016.10.11 1965
» 장기 트랜잭션 확인 및 경고 설정 jevida(강성욱) 2016.10.11 1523
1889 SQL 데이터 수집툴 DiagManager 사용법 jevida(강성욱) 2016.10.11 1386
1888 Windows Server 2012 R2 그룹 관리 서비스 계정 및 SQL 서버 jevida(강성욱) 2016.10.11 1890
1887 Kerbros 구성 관리자 툴 jevida(강성욱) 2016.10.11 1214
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2731
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1427
1884 TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3777
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2896
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 970
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1228
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 924
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 1061
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1477
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 2035
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 1004
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1091





XE Login