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

SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 23. 10:18

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가 증가하였을 때 메일을 발송하는 예제 쿼리이다.

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

http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

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 highest task allocation

and high task deallocation is most likely growing the tempdb. NOTE: Please do not kill system tasks

that may be showing up in the table below.

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

To stop the query from running, do the following:

1. Open SQL Server Management Studio

2. Connect to database engine using Windows Authentication

3. Click on "New Query"

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

5. Hit the F5 button to run the query

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

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.

<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/

2014-07-22 / 강성욱 / http://sqlmvp.kr

Sqlserver, tempdb, 데이터베이스, DBA, DB 모니터링, SQL Mail, 데이터 파일 증가, 템프DB, 성능 카운터, Job agent, 잡에이전트

'SQL Server > SQL Server Tip' 카테고리의 다른 글

RANDBETWEEN 함수 만들기 (0)	2015.07.23
Optimize for hint 쿼리 최적화 (0)	2015.07.23
블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 (0)	2015.07.23
다양한 포맷의 이름 파싱 하기 (0)	2015.07.23
비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 (0)	2015.07.23

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

Data Science Lab