blocked ID

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
amedexittAsked:
Who is Participating?
 
SireesConnect With a Mentor 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
0
 
gpompeCommented:
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

0
 
Eugene ZCommented:
<get a lock process or any id get blocked by another ID
please calrify:
Blocks- or deadlocks?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Eugene ZConnect With a Mentor 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
0
 
amedexittAuthor Commented:
I will say both of them
0
 
Eugene ZCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.