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

lusklusklusk發表於2020-01-07
SQLServer指令碼

--把程式碼中的所有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'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2672303/，如需轉載，請註明出處，否則將追究法律責任。

相關文章