用邮件发送运行时间久的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