Script to check database blocking - send auto email

  • Below is my script to check database blocking then will send automated email. I am getting this error: Msg 156, Level 15, State 1, Line 8

    Incorrect syntax near the keyword 'END'.

    What's the problem of my script? Thanks!

    Hope someone can enhance my script to add the sql statement of the spid. thanks in advance.

    select spid, blocked, waittype, waittime, lastwaittype, dbid, uid, cpu, physical_io, memusage, login_time, last_batch, hostname, program_name, nt_domain, nt_username, loginame from master..sysprocesses where blocked <> 0 and waittime > 60000 or spid in (select blocked from master..sysprocesses)

    IF @@ROWCouNT>= 1

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name= 'Database_Monitoring',

    @recipients= 'laurente.mary@yahoo.com',

    @subject= 'Database Blocking',

    @query= 'select spid, blocked, waittype, waittime, lastwaittype, dbid, uid, cpu, physical_io, memusage, login_time, last_batch, hostname, program_name, nt_domain, nt_username, loginame from master..sysprocesses where blocked <> 0 and waittime > 60000 or spid in (select blocked from master..sysprocesses)',

    END

  • You have a comma just before the END statement. Remove that.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • haha.. it's a minor mistake 🙂 thanks for your help!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply