Thursday, March 8, 2012

Blocking Alert

I'm trying to set up an alert for when a block occurs on a database. I
thought I could use error 1229, but that doesn't appear to be the correct
one, as it never fires, even tho we do get an occassional block.
Is there another error number I should be using? Is it possible to do what
I'm trying to do? Ideally, I would like an email to be sent when a block
occurs, so I can analyze the situation and make necessary changes.
Thanks for any advice,
TomTTom,
Are you refering to a database lock or a lock on an object in the database
that might be causing blocking? Might want to check out the SQL Server
Agent - Performance Condition Alerts - SQL Server:Locks.
HTH
Jerry
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
> I'm trying to set up an alert for when a block occurs on a database. I
> thought I could use error 1229, but that doesn't appear to be the correct
> one, as it never fires, even tho we do get an occassional block.
> Is there another error number I should be using? Is it possible to do what
> I'm trying to do? Ideally, I would like an email to be sent when a block
> occurs, so I can analyze the situation and make necessary changes.
> Thanks for any advice,
> TomT|||Jerry,
Thanks for your response. I'm talking about a block that shows up in EM,
under Current Activity\Process Info. There's a column for Blocking and
Blocked By.
"Jerry Spivey" wrote:

> Tom,
> Are you refering to a database lock or a lock on an object in the database
> that might be causing blocking? Might want to check out the SQL Server
> Agent - Performance Condition Alerts - SQL Server:Locks.
> HTH
> Jerry
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:581B2CA0-D62A-4DF0-BC06-8E597404D971@.microsoft.com...
>
>|||Check the following Knowledge Base article for a sample
script on setting this up:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/?id=271509
-Sue
On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
<tomt@.newsgroup.nospam> wrote:

>I'm trying to set up an alert for when a block occurs on a database. I
>thought I could use error 1229, but that doesn't appear to be the correct
>one, as it never fires, even tho we do get an occassional block.
>Is there another error number I should be using? Is it possible to do what
>I'm trying to do? Ideally, I would like an email to be sent when a block
>occurs, so I can analyze the situation and make necessary changes.
>Thanks for any advice,
>TomT|||Sue,
thanks very much for the link. Out of curiosity, is it possible to also set
an alert for this? Is there to your knowledge a specific error number for
this event?
thanks,
Tom
"Sue Hoegemeier" wrote:

> Check the following Knowledge Base article for a sample
> script on setting this up:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/?id=271509
> -Sue
> On Wed, 14 Sep 2005 15:08:04 -0700, "TomT"
> <tomt@.newsgroup.nospam> wrote:
>
>|||TomT wrote:
> Sue,
> thanks very much for the link. Out of curiosity, is it possible to
> also set an alert for this? Is there to your knowledge a specific
> error number for this event?
Blocking is not really an error condition. It's a normal part of RDBMS
operation. You are always going to have blocking going on in some
fashion. In a healthly system, the blocks are so short they are never
noticed. On a system with problems these blocks are more pronounced. The
blocking scipts Sue mentioned will help you determine when extended
blocks are occurring in the database.
You can set a lock timeout in you application if you want. With a lock
timeout, the application will only wait for a specific maximum time when
blocked before cancelling the query. A cancelled query throws an
attention event, which can be captured with Profiler or using a
server-side trace.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Tom,
Since many communities and MVP provide great answers, I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

No comments:

Post a Comment