Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to check database blocking - send auto email Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 9:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:13 PM
Points: 30, Visits: 200
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
Post #1359022
Posted Friday, September 14, 2012 2:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 916, Visits: 2,881
You have a comma just before the END statement. Remove that.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1359106
Posted Sunday, September 16, 2012 7:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:13 PM
Points: 30, Visits: 200
haha.. it's a minor mistake :) thanks for your help!
Post #1359945
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse