November 26, 2012 at 5:25 am
This is really beginning to annoy me.
I have Blocked Process Alerts on every single server in our environment. The alerts are set to email us when the blocked processes get above 2. So what happens? Our DEV server (not Production, not any of the others) is constantly sending emails out about the blocked processes.
I finally got annoyed and set up a job to try and catch the blocked processes (runs every 3 minutes). The problem is, the job isn't catching the events that trigger the alerts. In fact, it's catching other blocking events that don't trigger the alerts. I'm about at my wits end.
Does anyone have any suggestions on code that can assist me with catching whatever is causing the alerts?
The code I'm using I got from Ian Stirk's website / blog. It's attached for reference if you want to see it.
November 26, 2012 at 3:42 pm
Brandie,
I don't know the alert code you're using or if it's something like SQL monitor etc. but if I were in your spot I would run SQL Server Profiler against locked / blocked processes. I would write out what I needed to a table and correlate the alert times. Chances are the "Errors And Warnings" blocked process report is what you need.
Since its only development I doubt you have much of a concern regarding overhead from the profile. Even so there are other alternatives; like setting up a job to simply loop through who is active ( ). Alternately you could just make your own using sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
You would need to adjust the DMV calls to just look at blocked processes and write them to a custom table.
I currently have some intermittent network issues and am receiving alerts for failures; so I feel your pain with annoying alerts.
Teal Canady
Sr. DBA
Lieberman Research Worldwide
*Please excuse any spelling or grammatical mistakes; I'm just a DBA: )
November 26, 2012 at 4:55 pm
is the code you posted generating the alerts or what you are running to find the blocking every 3 min? if its one or the other the other half may be helpful to do a direct comparison.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 28, 2012 at 5:21 am
capnhector (11/26/2012)
is the code you posted generating the alerts or what you are running to find the blocking every 3 min? if its one or the other the other half may be helpful to do a direct comparison.
The code I posted is to locate the alerts. My issue is that I can't find out what is causing the alerts because the posted code does not report the alerts when they happen. It reports other blocked processes that don't send the alerts.
Which is why I'm frustrated.
I'm currently changing the job to run once a minute to see if that helps me catch the culprit, but I have doubts that will work. It's like the code is deliberately ignoring the problems that cause the email alerts.
November 28, 2012 at 5:25 am
TealCanady (11/26/2012)
I don't know the alert code you're using ....
Teal, I attached the code I'm using as a text file to my first post in this thread. It's hitting the sys.sysprocesses table.
Alternately you could just make your own using sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
Hmmm. Thank you for the recommendations. I'll look into these tables to see if they give me anything that sysprocesses isn't giving me.
November 28, 2012 at 11:12 am
Here is some modified active code using the DMV's I mentioned. I don't know how liberal you can be with your development environment but these are designed to run as SP_ in master. To execute it after you have created the procedure and function you can just use sp_active @@spid. I put it in my development environment for a more informative sp_who2 when the development teams blames the hardware. It should show you who is blocking who and for how long; what the wait type is etc.
USE [master]
GO
Create function [dbo].[Get_Statement]
(
@spid int
)
RETURNS varchar(8000)
AS
begin
declare @return varchar(8000)
SELECT @return = convert(varchar(8000),text)
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
where s.spid = @spid
Return ( @return )
End
GO
USE [master]
GO
CREATE proc [dbo].[sp_active]
( @spid int )
as
select
a.session_id,
a.blocking_session_id,
wait_time /1000 as WaitTime,
command,
[master].dbo.[Get_Statement](convert(int,a.session_id)) as CallToDB,
abs(a.total_elapsed_time /1000) as total_time_session,
host_name,
login_name,
a.row_count,
a.deadlock_priority,
a.reads,
a.writes,
percent_complete,
a.status,
database_id,
wait_type,
last_wait_type,
getdate() Time_Created
from sys.dm_exec_requests a
join sys.dm_exec_sessions b
on a.session_id = b.session_id
where a.session_id <> @spid and a.status <> 'background' and a.status <> 'Sleeping'
order by total_time_session desc
GO
December 26, 2012 at 12:04 pm
Teal,
Thanks for the code. I can be pretty liberal when it comes to this sort of thing, so long as I can prove it won't kill the system. I am looking at your code right now to see how it can be adapted to meet our needs.
December 26, 2012 at 1:27 pm
Below is code I use to get info on processes that are blocking and blocked:
SELECT DISTINCT
sysdatabases.name as database_name,
blocked.spid as blocked_spid,
blocked.waittime/1000 as wait_seconds,
convert(varchar(80),blocked.loginame) as blocked_loginame,
blocked.cmd as blocked_command,
convert(varchar(80),blocked.program_name) as blocked_program_name,
blocked.lastwaittype,
convert(varchar(80),blocked.waitresource) as waitresource,
blocking.spid as blocking_spid,
convert(varchar(80),blocking.loginame) as blocking_loginame,
blocking.cmd as blocking_command,
convert(varchar(80),blocking.program_name) as blocking_program_name,
(select text from sys.dm_exec_sql_text(blocked.sql_handle)) as blocked_sql_text,
(select text from sys.dm_exec_sql_text(blocking.sql_handle)) as blocking_sql_text,
blocked.waittime as blocked_waittime
from
sys.sysprocesses as blocking
join sys.sysprocesses as blocked on blocking.spid = blocked.blocked
join sys.sysdatabases on blocked.dbid = sysdatabases.dbid
WHERE blocked.blocked != 0
AND blocked.waittime >
case
when blocked.cmd like 'BACKUP%' and blocking.cmd like 'BACKUP%'
then <milli_seconds>
when blocked.program_name like 'SQLDMO_1%' and ( blocking.cmd like 'DBCC%' or blocking.program_name like 'SQLDMO_1%' )
then <milli_seconds>
else <milli_seconds>
end
AND blocked.spid != blocked.blocked
ORDER BY blocked.waittime DESC
December 26, 2012 at 5:12 pm
December 27, 2012 at 6:14 am
arnipetursson, thank you for your script, but it's using the same tables I'm already using even if the SELECT is a bit different. The Blocked Processes alert apparently doesn't use the sysprocesses table, which is my problem.
@SQLFRNDZ, does this script touch the same tables used to generate the system "Blocked Processes" alert?
September 6, 2016 at 6:30 pm
What was the outcome? We are having a similar issue, but I can't capture the block.
September 15, 2016 at 7:12 am
mcliffordDBA (9/6/2016)
What was the outcome? We are having a similar issue, but I can't capture the block.
Oh, wow. It's been so long ago. We ended up having to do an SP_WHO2 every time we got alerted to a block and it turned out to be our reporting team running long queries. Queries that even blocked themselves.
Try this. I don't remember what website I got this off of, but I love being able to change the WHERE clause to look at specific DBs or blocked process only, etc.
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SELECT * FROM @Table
--WHERE --dbname = 'MyDB'
--ProgramName LIKE 'Microsoft%' AND
--LOGIN NOT LIKE '%\%' AND HostName <> 'MyPC'
ORDER BY DBName, BlkBy, SPID --LOGIN DESC, HostName;
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply