用邮件发送运行时间久的SQL语句

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