Blocked Process Alert issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Blocked Process Alert issues

11 posts, Page 1 of 2

1 2 »»

Blocked Process Alert issues

Rate Topic

Display Mode

Topic Options

Author

Message

Brandie Tarvin

Posted Monday, November 26, 2012 5:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 6,655, Visits: 5,678

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.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post Attachments

Find Blocked and Blocking Processes Code.txt (23 views, 3.46 KB)

Post #1388551

TealCanady

Posted Monday, November 26, 2012 3:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:04 PM
Points: 9, Visits: 385

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 ( [url=http://sqlblog.com/files/folders/release/entry35240.aspx][/url] ). 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: )

Post #1388877

CapnHector

Posted Monday, November 26, 2012 4:55 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1388897

Brandie Tarvin

Posted Wednesday, November 28, 2012 5:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 6,655, Visits: 5,678

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.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1389655

Brandie Tarvin

Posted Wednesday, November 28, 2012 5:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 6,655, Visits: 5,678

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.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1389658

TealCanady

Posted Wednesday, November 28, 2012 11:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:04 PM
Points: 9, Visits: 385

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

Post #1390007

Brandie Tarvin

Posted Wednesday, December 26, 2012 12:04 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 6,655, Visits: 5,678

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.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1400322

arnipetursson

Posted Wednesday, December 26, 2012 1:27 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:23 PM
Points: 135, Visits: 413

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

Post #1400359

@SQLFRNDZ

Posted Wednesday, December 26, 2012 5:12 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:03 PM
Points: 412, Visits: 862

Check this activity monitor script and it can find you the root blocker and you know how to tweek it to get the query running by root blocker.

Hope it helps !

--SQLFRNDZ

Post #1400417

Brandie Tarvin

Posted Thursday, December 27, 2012 6:14 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 6,655, Visits: 5,678

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?

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1400591

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions