Post reply

Script to check database blocking - send auto email

  • September 13, 2012 at 9:08 pm

    #266571

    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

  • September 14, 2012 at 2:08 am

    #1537673

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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • September 16, 2012 at 7:48 pm

    #1538377

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

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

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