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

 

  • 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 19839
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12110
2314 MSSQL과 Oracle 함수 비교 3탄 - 9가지 함수(마지막) [5] 쓸만한게없네(윤선식) 2010.11.08 82791
2313 SSMS 줄 번호 표시 jevida(강성욱) 2015.03.26 64816
2312 DB 오류검사기록 테이블, 백업할때 오류정보 기록테이블 [3] 차주언 2011.07.20 56517
2311 10_SQL 2008 강좌 - (1) DB관리 - DB 축소(SHRINK) [8] jevida(강성욱) 2011.03.01 52602
2310 Linked Server Open Query 이용 시 USE 명령어 사용하기 [1] 쓸만한게없네(윤선식) 2012.11.07 51162
2309 sa 비밀번호를 잊어먹었을 때 대처방법 [8] 이스트럭(강동운) 2011.06.13 48273
2308 [TIP]MSSQL 와일드 카드를 이용한 검색과 주의점 [16] 쓸만한게없네 2010.10.12 45424
2307 SMO를 이용한 DB백업 [3] 우주인(김재훈) 2011.03.17 45096
2306 SQL Server 로긴 유저 및 각 DB별 사용자 권한 조회 [1] 문연군 2010.12.20 44158
2305 프로파일러 사용법. (완전 초보자 기준!) [10] jevida(강성욱) 2011.04.26 43936
2304 SSMS 단축키. [12] 쓸만한게없네(윤선식) 2011.02.07 43796
2303 MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수 [10] 쓸만한게없네(윤선식) 2010.10.27 43140
2302 Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 42367
2301 DBCC TRACE ... [4] 이스트럭(강동운) 2012.04.13 40995
2300 CONVERT 스타일 별 결과 값 [3] Alucard(강산아) 2010.10.14 39959
2299 SQL Server 와 MySQL 문자열 함수 비교 10 가지 [5] 이호엽 2010.12.29 38548
2298 SQL2012는 SSMS의 시작 옵션(Startup option) 설정 위치가 바뀌었어요. [1] 코난(김대우) 2012.01.13 37501
2297 MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수. [7] 쓸만한게없네(윤선식) 2010.10.25 36040
2296 트랜잭션 열린넘 있으면 로그백업할때 사이즈가 커진다!! [5] 차주언 2009.12.02 33603
2295 초간단 - 알만한 사람은 다 아는 읽고, 쓰고, 실행하는 권한만 주기 스크립트. [2] 쓸만한게없네(윤선식) 2011.07.21 32968





XE Login