October 13, 2008 at 3:41 pm
Please advice if some automatic monitoring mechanism or scripts can be put in place to detect the blocked processes on any database. It would be better to get alerted for this rather than the business users getting back to me all the time.
Please advice if you have inputs on this.
Thanks in advance for all your help.
M&M
October 13, 2008 at 11:40 pm
I generally use this script to monitor blocking.You can create a job with this script and monitor it.
--To list all the blocking threads
SELECT s.Text, p.*
FROM sys.dm_exec_requests e with (nolock)
right outer join sys.sysprocesses p with (nolock)
on e.session_id = p.spid --and e.session_id = @@spid
CROSS APPLY sys.dm_exec_sql_text(e.sql_handle) s
WHERE 1=1
--and (p.blocked > 0 or p.spid in (select blocked from sys.sysprocesses))
--and uid is not null
order by case blocked when 0 then 1 else 2 end, waittime desc
"More Green More Oxygen !! Plant a tree today"
October 14, 2008 at 4:52 am
Like you mentioned, create an alert:
Location in management studio: SQL SERVER->SQL Server Agent->Alerts
* GENERAL TAB*
type : SQL server performance condition alert
object: SQL Server: General statistics
counter: processes blocked
alert if rises above 0
*RESPONSE TAB*
notify operator (make sure operators and mail are defined)
Wilfred
The best things in life are the simple things
October 14, 2008 at 5:12 am
use master
create proc sp_who3
as begin
select 'dbcc inputbuffer('+convert(varchar(10),spid)+')' as inputbuffer,waittime/(1000*60) as minutes, *
from(select * from master.dbo.sysprocesses
where blocked > 0
union
select * from master.dbo.sysprocesses
where spid in (select blocked from master.dbo.sysprocesses
where blocked > 0)
) as tmp
order by blocked , spid
end
and then run sp_who3 any database it is easily identify the blocks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply