Script to check database blocking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Script to check database blocking - send auto...

Script to check database blocking - send auto email

Rate Topic

Display Mode

Topic Options

Author

Message

marygrace_laurente23

Posted Thursday, September 13, 2012 9:08 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:30 AM
Points: 27, Visits: 124

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