ALTER proc [dbo].[usp_EmailLongRunningSQL]
as
begin
declare@LongRunningQueries AS TABLE
(
lrqId int IDENTITY(1,1) PRIMARY KEY,
spid int NULL,
batch_duration bigintNULL,
program_namenvarchar(500) NULL,
hostname nvarchar(100) NULL,
loginame nvarchar(100) NULL,
sqltext nvarchar(max) NULL
)
-- variabledeclaratuions
DECLARE @exectime DATETIME
DECLARE @tableHTMLNVARCHAR(MAX)
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
DECLARE @sqltext NVARCHAR(MAX)
DECLARE @timeLimitsmallint
declare @Sub as varchar(100)
set @Sub = @@servername + 'Long Running Query found'
SET @timeLimit=(3*60) -- minutes
-- WAITFOR DELAY'00:01:05' -- uncomment for testing (1min:5sec)
-- populate thetable with execution info, you don't have to use top 1
INSERT INTO @LongRunningQueries(spid, batch_duration, program_name, hostname, loginame)
SELECT top 5
P.spid
, convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000
, P.program_name
, P.hostname
, P.loginame
FROM master.dbo.sysprocesses P WITH(NOLOCK)
WHERE (P.spid > 50)
AND P.status NOT IN('background', 'sleeping')
AND P.cmd NOT IN('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')
AND convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000 >@timeLImit
-- use a cursorto update the sqltext for each pid
DECLARE @lrqId int
DECLARE mycur cursor for
SELECT lrqId from @LongRunningQueries
ORDER BY lrqId
OPEN mycur
FETCH NEXT FROM mycur INTO @lrqId
WHILE @@FETCH_STATUS=0
BEGIN
SET @SPID =(SELECT spid from @LongRunningQueries WHERElrqId=@lrqId)
-- get the SQL theSPID is executing
SELECT@Handle = sql_handleFROM master.dbo.sysprocesses WHEREspid = @SPID
UPDATE@LongRunningQueries
SET sqltext =(SELECT text FROM sys.dm_exec_sql_text(@Handle))
WHERE lrqId = @lrqId
FETCH NEXT FROM mycur INTO @lrqId
END
CLOSE mycur
DEALLOCATE mycur
DELETE FROM @LongRunningQueries WHEREsqltext IS NULLOR sqltext='' OR program_nameLIKE '%DatabaseMail%'
IF EXISTS(SELECT * FROM@LongRunningQueries WHERE sqltext IS NOT NULL OR sqltext<>'')
BEGIN
-- populate atable with it's info and mail it
SET @tableHTML=
N'<H1>LongRunning Querys</H1>' +
N'<tableborder="1">' +
N'<tr><th>SPID</th>'+
N'<th>Duration</th>'+
N'<th>Application</th>'+
N'<th>HostName</th>'+
N'<th>Login</th>'+
N'<th>SQLExecuting</th></tr>' +
CAST(( SELECT td = T.spid, '',
td = T.batch_duration, '',
td = T.[program_name], '',
td = T.hostname, '',
td = T.loginame, '',
td = T.sqltext, ''
FROM
@LongRunningQueries T
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
-- if @tableHTMLis NULL, mail will not get sent
EXEC msdb.dbo.sp_send_dbmail,
@recipients= 'smith.liu@126.com'
@body = @tableHTML,
@body_format = 'HTML';
END
end
使用邮件发送运行时间久的SQL语句
最新推荐文章于 2022-04-27 16:03:30 发布