We help IT Professionals succeed at work.

blocked ID

amedexitt
amedexitt asked
on
Medium Priority
327 Views
Last Modified: 2010-08-05
Hi there

How can I create an Alert or any other thing that advise me every time that the server (SQL 2000) get a lock process or any id get blocked by another ID

Thanks
Comment
Watch Question

Commented:
How about creating a SP like this and create a new alert with this SP ?

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Deadlock Occurance'))
 ---- Delete the alert with the same name.
  EXECUTE msdb.dbo.sp_delete_alert @name = N'Deadlock Occurance'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlock Occurance', @message_id = 8901, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'
END

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
You can use something like this (changing the email and names if you want):

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
            @enabled=1,
            @pager_days=0,
            @email_address=N'DBA@XXX.com'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Locking',
            @enabled=1,
            @delay_between_responses=0,
            @include_event_description_in=0,
            @performance_condition=N'SQLServer:Locks|Lock Wait Time (ms)|_Total|>|1000',
            @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Locking', @operator_name=N'DBA', @notification_method = 1
GO

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
<get a lock process or any id get blocked by another ID
please calrify:
Blocks- or deadlocks?
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
for deadlock you can try code below and adjust it to your requirments:

---create operator as per gpompe's post  
use MSDB

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'deadlock'))
 ---- Delete the alert with the same name.
  EXECUTE msdb.dbo.sp_delete_alert @name = N'deadlock'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'deadlock', @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 60, @performance_condition = N'SQLServer:Locks|Number of Deadlocks/sec|Database|>|0', @include_event_description_in = 5, @category_name = N'[Uncategorized]'

EXECUTE msdb.dbo.sp_add_notification @alert_name = N'deadlock', @operator_name = N'', @notification_method = 1

END

Author

Commented:
I will say both of them
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
Deadlocking is more than blocking. Blocking occurs when one thread is waiting on another, and some brief blocking is normal.

A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other's resource in order to finish a transaction. The result is mutual blocking: each waits on the other to acquire some resource that the other process already has.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.