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
