blocked ID

Posted on 2006-10-23
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

Question by:amedexitt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 20

Accepted Solution

Sirees earned 250 total points
ID: 17789540
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'
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]'

Expert Comment

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

USE [msdb]
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
EXEC msdb.dbo.sp_add_alert @name=N'Locking',
            @performance_condition=N'SQLServer:Locks|Lock Wait Time (ms)|_Total|>|1000',
EXEC msdb.dbo.sp_add_notification @alert_name=N'Locking', @operator_name=N'DBA', @notification_method = 1

LVL 43

Expert Comment

by:Eugene Z
ID: 17790501
<get a lock process or any id get blocked by another ID
please calrify:
Blocks- or deadlocks?
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 250 total points
ID: 17790559
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'
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


Author Comment

ID: 17790865
I will say both of them
LVL 43

Expert Comment

by:Eugene Z
ID: 17791693
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.

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question