敗家貓

mssql_ck_lock

發表於 分類於

SQL檢查Lock發出Mail告警

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
use master

DECLARE @htmlTable NVARCHAR(MAX); --被影響的session的html格式的資料
DECLARE @RootHtmlTable NVARCHAR(MAX); --造成lock的源頭session的html格式的資料
DECLARE @AllHtmlTable NVARCHAR(MAX); -- @RootHtmlTable+@htmlTable

SET @AllHtmlTable=N'';

SET @htmlTable = N'
被影響的Session
<table border="1">    
    <tr>
        <th>DB_NAME</th>
        <th>session_id</th>
        <th>login_name</th>
        <th>login_time</th>
        <th>command</th>
        <th>wait_type</th>
        <th>text</th>
        <th>host_name</th>
        <th>program_name</th>
        <th>nt_user_name</th>
        <th>start_time</th>
        <th>last_request_start_time</th>
        <th>last_request_end_time</th>
    </tr>
';

SET @RootHtmlTable = N'
源頭的Session
<table border="1">   
    <tr>
        <th>DB_NAME</th>
        <th>session_id</th>
        <th>login_name</th>
        <th>login_time</th>
        <th>command</th>
        <th>wait_type</th>
        <th>text</th>
        <th>host_name</th>
        <th>program_name</th>
        <th>nt_user_name</th>
        <th>start_time</th>
        <th>last_request_start_time</th>
        <th>last_request_end_time</th>
    </tr>
';

DECLARE @DB_NAME NVARCHAR(128);
DECLARE @session_id INT;
DECLARE @login_name NVARCHAR(128);
DECLARE @login_time DATETIME;
DECLARE @command NVARCHAR(MAX);
DECLARE @wait_type NVARCHAR(60);
DECLARE @text NVARCHAR(MAX);
DECLARE @host_name NVARCHAR(128);
DECLARE @program_name NVARCHAR(128);
DECLARE @nt_user_name NVARCHAR(128);
DECLARE @start_time DATETIME;
DECLARE @last_request_start_time DATETIME;
DECLARE @last_request_end_time DATETIME;
DECLARE @ct_number INT;
DECLARE @mail_addr NVARCHAR(1000);

DECLARE @SessionIDs TABLE (SessionID INT); --被影響的session的陣例
DECLARE @SessionID INT; --被影響的session

DECLARE @blockingSessionIDs TABLE (blockingSessionID INT); --存放造成lock的源頭session陣例
DECLARE @blockingSessionID INT;
INSERT INTO @blockingSessionIDs (blockingSessionID) SELECT distinct blocking_session_id FROM sys.dm_exec_requests
                                                                                       WHERE blocking_session_id > 0 --取出現在造成lock的源頭session
																					     AND DATEDIFF(second, start_time, GETDATE()) > 300; --Lock情況發生超過300秒才會寄告警Mail

DECLARE blockingSessionCursor CURSOR FOR SELECT blockingSessionID FROM @blockingSessionIDs;
OPEN blockingSessionCursor;
FETCH NEXT FROM blockingSessionCursor INTO @blockingSessionID;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '源頭 Session ID: ' + CAST(@blockingSessionID AS VARCHAR(10));
	DELETE FROM @SessionIDs;
	INSERT INTO @SessionIDs (SessionID)
                select distinct session_id from  sys.dm_exec_requests 
                                                     where blocking_session_id = (CAST(@blockingSessionID AS VARCHAR(10)));                                                       

	SELECT
         @DB_NAME = DB_NAME(der.database_id),
         @session_id = der.session_id,
         @login_name = des.login_name,
         @login_time = des.login_time,
         @command = der.command,
         @wait_type = der.wait_type,
         @text = SUBSTRING(qt.text,1,250),
         @host_name = des.host_name,
         @program_name = des.program_name,
         @nt_user_name = des.nt_user_name,
         @start_time = der.start_time,
         @last_request_start_time = des.last_request_start_time,
         @last_request_end_time = des.last_request_end_time
     FROM
         sys.dm_exec_requests der
     JOIN
         sys.dm_exec_sessions des ON der.session_id = des.session_id
     CROSS APPLY
         sys.dm_exec_sql_text(der.plan_handle) AS qt
     WHERE
         der.session_id =CAST(@blockingSessionID AS VARCHAR(10));

     SET @AllHtmlTable = @AllHtmlTable + @RootHtmlTable + N'
         <tr>
             <td>' + @DB_NAME + N'</td>
             <td>' + CAST(@session_id AS NVARCHAR(10)) + N'</td>
             <td>' + @login_name + N'</td>
             <td>' + CONVERT(NVARCHAR(19), @login_time, 120) + N'</td>
             <td>' + @command + N'</td>
             <td>' + @wait_type + N'</td>
             <td>' + @text + N'</td>
             <td>' + @host_name + N'</td>
             <td>' + @program_name + N'</td>
             <td>' + @nt_user_name + N'</td>
             <td>' + CONVERT(NVARCHAR(19), @start_time, 120) + N'</td>
             <td>' + CONVERT(NVARCHAR(19), @last_request_start_time, 120) + N'</td>
             <td>' + CONVERT(NVARCHAR(19), @last_request_end_time, 120) + N'</td>
         </tr>';
	 SET @AllHtmlTable = @AllHtmlTable + N'</table>'; --組出造成lock的源頭session的html格式的資料
		  		 
    SET @ct_number = 0; --開始處理被影響的session的html格式的資料
  	DECLARE SessionIDCursor CURSOR FOR SELECT SessionID FROM @SessionIDs;
  	OPEN SessionIDCursor;
    FETCH NEXT FROM SessionIDCursor INTO @SessionID;
  	WHILE @@FETCH_STATUS = 0
      BEGIN
         PRINT '被影響 Session ID: ' + CAST(@SessionID AS VARCHAR(10));
  
  		 SELECT
              @DB_NAME = DB_NAME(der.database_id),
              @session_id = der.session_id,
              @login_name = des.login_name,
              @login_time = des.login_time,
              @command = der.command,
              @wait_type = der.wait_type,
              @text = SUBSTRING(qt.text,1,250),
              @host_name = des.host_name,
              @program_name = des.program_name,
              @nt_user_name = des.nt_user_name,
              @start_time = der.start_time,
              @last_request_start_time = des.last_request_start_time,
              @last_request_end_time = des.last_request_end_time
          FROM
              sys.dm_exec_requests der
          JOIN
              sys.dm_exec_sessions des ON der.session_id = des.session_id
          CROSS APPLY
              sys.dm_exec_sql_text(der.plan_handle) AS qt
          WHERE
              der.session_id =CAST(@SessionID AS VARCHAR(10));
			  
          if  @ct_number > 0 
			  SET @AllHtmlTable = @AllHtmlTable + N'
                  <tr>
                      <td>' + @DB_NAME + N'</td>
                      <td>' + CAST(@session_id AS NVARCHAR(10)) + N'</td>
                      <td>' + @login_name + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @login_time, 120) + N'</td>
                      <td>' + @command + N'</td>
                      <td>' + @wait_type + N'</td>
                      <td>' + @text + N'</td>
                      <td>' + @host_name + N'</td>
                      <td>' + @program_name + N'</td>
                      <td>' + @nt_user_name + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @start_time, 120) + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @last_request_start_time, 120) + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @last_request_end_time, 120) + N'</td>
                  </tr>';
	    else 
              SET @AllHtmlTable = @AllHtmlTable + @htmlTable + N'
                  <tr>
                      <td>' + @DB_NAME + N'</td>
                      <td>' + CAST(@session_id AS NVARCHAR(10)) + N'</td>
                      <td>' + @login_name + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @login_time, 120) + N'</td>
                      <td>' + @command + N'</td>
                      <td>' + @wait_type + N'</td>
                      <td>' + @text + N'</td>
                      <td>' + @host_name + N'</td>
                      <td>' + @program_name + N'</td>
                      <td>' + @nt_user_name + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @start_time, 120) + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @last_request_start_time, 120) + N'</td>
                      <td>' + CONVERT(NVARCHAR(19), @last_request_end_time, 120) + N'</td>
                  </tr>'; 
  
  	  FETCH NEXT FROM SessionIDCursor INTO @SessionID;		  
	  set @ct_number = @ct_number +1;
    END
  	CLOSE SessionIDCursor;
    DEALLOCATE SessionIDCursor;
	SET @AllHtmlTable = @AllHtmlTable+N'</table>';

    FETCH NEXT FROM blockingSessionCursor INTO @blockingSessionID;
	SET @AllHtmlTable = @AllHtmlTable + N'<br></br>'+ N'<br></br>';
END

CLOSE blockingSessionCursor;
DEALLOCATE blockingSessionCursor;

if @ct_number >0
   begin
        SELECT @mail_addr=email_address FROM msdb.dbo.sysoperators WHERE [name] = 'check_lock_mail_users';
        
        DECLARE @HostName NVARCHAR(128);
        SELECT @HostName = CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName'));
        
        DECLARE @Subject NVARCHAR(200) = CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName'))+'--Check Lock Session--'+CONVERT(NVARCHAR(30), GETDATE(), 120);
        DECLARE @Body NVARCHAR(MAX) = @AllHtmlTable;
        
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = '郵件配置的名稱',
            @recipients = @mail_addr,
            @subject = @Subject,
            @body = @Body,
            @body_format = 'HTML';
   end;

預設的extended events還是不怎好用，只好手動寫一隻Mail告警Lock