--把程式碼中的所有XX換成你需要的等待事件型別比如SOSHOST_MUTEX即可

IF NOT EXISTS (
select @@servername,db_name(dbid) db_name,lastwaittype wait_name,
spid,loginame,login_time,status,hostname,program_name,cmd
from sys.sysprocesses where lastwaittype like '%XX%'
   )
   RETURN

DECLARE @Body NVARCHAR(MAX)

SET @Body =
'<html>
<body style="font-family:Arial">
<table style="font-family:Arial; border-collapse: collapse">
   <thead style="font-size:14px; color:#FFF">
       <tr>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">servername</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">db_name</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">wait_name</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">spid</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">loginame</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">login_time</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">status</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">hostname</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">program_name</th>
           <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">cmd</th>
           </tr>
   </thead>
   <tbody style="font-size:12px; color:#666">
'

SELECT
   @Body +=
'   <tr>
           <td style="border:1px solid #ddd; padding:6px 10px">' + @@servername + '</td>
           <td style="border:1px solid #ddd; padding:6px 10px">' + db_name(dbid) + '</td>
            <td style="border:1px solid #ddd; padding:6px 10px">'+ lastwaittype +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ convert(varchar,spid) +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ loginame +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ CONVERT(varchar(50), login_time, 0) +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ status +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ hostname +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ program_name +' </td>
           <td style="border:1px solid #ddd; padding:6px 10px">'+ cmd +' </td>
       </tr>
'
from sys.sysprocesses where lastwaittype like '%XX%'

SET @Body +=
'   </tbody>
</table>
</body>
</html>'

PRINT @Body

EXECUTE msdb.dbo.sp_send_dbmail
   @subject = N'SOSHOST_MUTEX ON PANARAYWONDBALIAS\MARKETDATA',
   @recipients = N'lukes1@william.com;lukes2@william.com',
        @copy_recipients = N'lukes3@william.com;lukes4@william.com',
   @body = @Body,
   @body_format = N'HTML'

sqlserver監控指令碼_發現某個等待就發出郵件

相關文章