데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.


TempDB 파일 사이즈 증가 시 경고 받기

 

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

 

 

SQL Server를 운영하면서 모니터링해야 할 항목들이 있다. 특히 시스템 데이터베이스의 경우 SQL Server를 운영하는데 필수적인 사항으로 이상이 발견되었을 때 즉시 알림을 받을 수 있도록 해야 한다.

 

이번 포스트는 시스템 데이터베이스에서 가장 많이 사용되고 있는 tempdb의 파일 사이즈 증가 시 알림을 받기 위한 방법으로 SQL Serve Agent 기능을 활용하는 방법에 대해서 알아본다.

 

경고 작업을 생성하기 위해 SSMS를 실행하여 SQL Server Agent에서 경고를 선택 한다. 경고에 사용할 이름을 입력하고 유형을 선택 한다. 데이터파일의 증가는 성능 모니터 정보를 검사 할 수 있도록 performance condition alert를 선택 한다.

 

카운터의 이름을 입력한다. (대소문자 주의) 그리고 모니터링 하려는 대상의 인스턴스명을 입력 한다. 데이터파일의 증가에 대한 경고가 목적이기 때문에 초과(rises above) 를 선택한다. 실습에서는 초과 값을 10000킬로바이트(단위 : KB)로 설정 하였다.

 


응답 탭으로 이동하여 체크박스를 선택하고 [새 작업]을 클릭하여 작업을 생성한다. 작업을 생성하는 과정은 SQL Server Agent 작업 등록과 동일하다. (작업 등록 완료 후 SQL Server Agent에도 등록되어 있음을 확인 할 수 있다.)

 

작업의 이름을 등록 한다.

 

 

단계 이름을 설정하고 해당 이벤트가 발생 했을 때 실행 할 쿼리를 입력 한다. 아래 예제 쿼리는 tempdb가 증가하였을 때 메일을 발송하는 예제 쿼리이다.

 

데이터베이스 메일에 대한 설정은 다음 아티클을 참고 한다.

 

 

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST((

SELECT TOP 5 --Change number accordingly

su.Session_ID AS 'td','',

ss.Login_Name AS 'td','',

rq.Command AS 'td','',

su.Task_Alloc AS 'td','',

su.Task_Dealloc AS 'td','',

--Find Offending Query Text:

(SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max),text)) * 2

ELSE statement_end_offset

END - rq.statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS 'td'

FROM

(SELECT su.session_id, su.request_id,

SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

FROM sys.dm_db_task_space_usage AS su

GROUP BY session_id, request_id) AS su,

sys.dm_exec_sessions AS ss,

sys.dm_exec_requests AS rq

WHERE su.session_id = rq.session_id

AND(su.request_id = rq.request_id)

AND (ss.session_id = su.session_id)

AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed

AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

ORDER BY su.task_alloc DESC --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth

FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))

--BODY OF EMAIL - Edit for your environment

SET @body ='<html><H1>Tempdb Large Query</H1>

<body bgcolor=white>The query below with the <u>highest task allocation

and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b>

that may be showing up in the table below.

<U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U><BR>

<BR>

To stop the query from running, do the following:<BR>

<BR>

1. Open <b>SQL Server Management Studio</b><BR>

2. <b>Connect to database engine using Windows Authentication</b><BR>

3. Click on <b>"New Query"</b><BR>

4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this: KILL 537; <BR>

5. Hit the <b>F5</b> button to run the query<BR>

<BR>

This should kill the session/query that is growing the large query. It will also kick the individual out of the application.<BR>

You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.

<BR>

<BR>

<table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>'

SET @body = @body + @xml +'</table></body></html>'

--Send email to recipients:

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'dba@domain.com', --Insert the TO: email Address here

@copy_recipients ='dba_Manager@domain.com', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)

@body = @body,@body_format ='HTML',

@importance ='High',

@subject ='THIS IS A TEST', --Provide a subject for the email

@profile_name = 'DatabaseMailProfile' --Database Mail profile here

 

 

작업 생성이 완료 되었으면 위에서 생성한 작업을 선택하고 확인을 클릭한다.

 

 

Tempdb가 증가 하였을 때 메일이 발송되며 메일은 다음과 같은 형식이다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3276/sql-server-alert-for-tempdb-growing-out-of-control/

 



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

No. Subject Author Date Views
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1587
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3033
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1368
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1533
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1781
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1436
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1331
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1745
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1493
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1783
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2953
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3186
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2400
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4640
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3512
» TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3047
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3250
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2666
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3151





XE Login