September 4, 2007 at 5:29 pm
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/pleblanc/3243.asp
October 10, 2007 at 4:03 am
Try this stored procedure:
(where PerfDB is my database for differend kind of administrative task)
and make a job with that stored procedure, and run this job at every minute, and you will have a real time monitor of bloking.
USE [PerfDB]
GO
/****** Object: StoredProcedure [dbo].[BD_Monitorizare_Blocaje] Script Date: 10/10/2007 12:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[BD_Monitorizare_Blocaje]
as
declare @spid int
declare @blk_by int
declare @spid2 int
declare @buffer1 varchar(8000)
declare @buffer2 varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @login1 varchar(50)
declare @login2 varchar(50)
declare @database_name varchar(20)
declare @dbid int
declare @sql5 varchar(8000)
declare @sql6 varchar(8000)
declare @hostname1 varchar(100)
declare @hostname2 varchar(100)
declare @program_name1 varchar(100)
declare @program_name2 varchar(100)
create table #BD_Monitorizare_Procese
(
spid varchar(50),
login varchar(100),
hostname varchar(100),
program_name varchar(100),
blk_by varchar(100),
dbid varchar(100),
last_batch datetime
)
insert into #BD_Monitorizare_Procese
select
spid
,convert(sysname, rtrim(loginame))
,hostname
,program_name
,blocked
,dbid
, last_batch
from master.dbo.sysprocesses with (nolock)
where len(hostname)>0
DECLARE xcursor CURSOR FOR
SELECT spid FROM #BD_Monitorizare_Procese order by spid
OPEN xcursor
FETCH NEXT FROM xcursor INTO @spid
WHILE (@@fetch_status = 0)
BEGIN
select @blk_by = blk_by from #BD_Monitorizare_Procese where spid=@spid
if @blk_by <> 0
begin
select @login1 = login from #BD_Monitorizare_Procese where spid=@spid
select @login2 = login from #BD_Monitorizare_Procese where spid=@blk_by
select @dbid = dbid from #BD_Monitorizare_Procese where spid=@spid
select @database_name = name from sys.sysdatabases where dbid=@dbid
select @hostname1 = hostname from #BD_Monitorizare_Procese where spid=@spid
select @hostname2 = hostname from #BD_Monitorizare_Procese where spid=@blk_by
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@spid
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@blk_by
create table #dbcc(c1 varchar(20), c2 int, c3 varchar(8000))
insert #dbcc EXEC('dbcc inputbuffer('+@spid+')')
select @buffer1 = c3 from #dbcc
delete from #dbcc
insert #dbcc EXEC('dbcc inputbuffer('+@blk_by+')')
select @buffer2 = c3 from #dbcc
drop table #dbcc
set @buffer1 = replace(@buffer1,'''','`')
set @buffer2 = replace(@buffer2,'''','`' )
--
--declare @buffer1 varchar(8000)
--declare @buffer2 varchar(8000)
--declare @sql1 varchar(8000)
--declare @sql2 varchar(8000)
--declare @login1 varchar(50)
--declare @login2 varchar(50)
--declare @database_name varchar(20)
--declare @dbid int
--declare @sql5 varchar(8000)
--declare @sql6 varchar(8000)
--declare @hostname1 varchar(100)
--declare @hostname2 varchar(100)
--declare @program_name1 varchar(100)
--declare @program_name2 varchar(100)
--set @database_name='fffff'
--set @login1='ddddd'
--set @login2='gggggg'
--set @hostname1='hhhhhhh'
--set @hostname2='sssss'
--set @buffer1='423423423324'
--set @buffer2='55345323552'
exec('exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!''')
--print 'exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!'''
-- set @sql1= 'EXEC master..xp_cmdshell ''net send CBOGDUM '''' Este un blocaj pe baza '+@database_name+' userul '+@login1+' este blocat de '+@login2+' !!'''
-- exec (@sql1)
--
-- set @sql2=
-- 'EXECUTE [msdb].[dbo].[sp_send_dbmail]
-- @profile_name = ''MailAccount''
-- ,@recipients = ''bogdan.dumitru@railoc.ro''
-- ,@body = '' Este un blocaj pe baza '+@database_name+'! Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+char(13)+char(13)+'Userul '+@login1+' executa: '+char(13)+@buffer1+char(13)+char(13)
-- +' Userul '+@login2+' executa: '+char(13)+@buffer2+'''
-- ,@subject = ''BLOCAJ!'''
-- exec (@sql2)
insert into BD_Monitorizare_Blocaje_tbl ( database_name, login1_blocat, login2_care_blocheaza, hostname_login1, hostname_login2, buffer_login1, buffer_login2 )
values (@database_name, @login1, @login2, @hostname1, @hostname2, @buffer1, @buffer2 )
FETCH NEXT FROM xcursor INTO @spid
end
else
FETCH NEXT FROM xcursor INTO @spid
END
CLOSE xcursor
DEALLOCATE xcursor
--create table BD_Monitorizare_Blocaje_tbl
--(
--data datetime default getdate(),
--database_name varchar(20),
--login1_blocat varchar(50),
--login2_care_blocheaza varchar(50),
--hostname_login1 varchar(50),
--hostname_login2 varchar(50),
--buffer_login1 varchar(8000),
--buffer_login2 varchar(8000),
--program_name1 varchar(100),
--program_name2 varchar(100)
--)
October 10, 2007 at 6:44 am
"For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used."
It means that initially you may have some not 0 values and the proc dbo.usp_DeadlockNotification
will gives the alerts each time.
You should use difference in time interval.
October 10, 2007 at 7:18 am
I haven't tried it yet, but could you also set up an Alert in SQL Server 2005 where the Object is SQL Server:Locks and the Counter is Number of Deadlocks/sec with a condition of if rises above 0. Then you could subsrcibe to the Alert?
October 10, 2007 at 7:24 am
why not just use event notfications that are available in sql 2005? If you monitor the deadlock graph event you can get an alert that contains information about the deadlock. The alert can be a realtime email or just storing the data in a table which you can check once a day.
This is a lot easier than writing a job which checks the details every minute or half an hour and contains all of the information you need to track down where the problem is.
Tony Rogerson has written an excellent article on to set up event notifications for blocking events at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx
It fairly easy to change that to report on deadlock_graph event instead
October 10, 2007 at 7:45 am
Great article!
As for the DMV, I concur with LP. The sys.dm_os_performance_counters view is constantly incrementing various counters of performance that SQL Server reports to the O/S. As written, after the first deadlock occurs the proc will signal an e-mail every time the job runs. If you capture the results at the last run of the sproc, then compare the current run against those values you'll have an accurate measure of deadlocks in the interval between job runs. The you can signal an e-mail only when there's an increase in the counter since the last run.
As for the volatility of the values in the DMV, they are all reset to 0 with a reboot or an instance restart.
Thanks for the article, and I hope this helps!
Carter
But boss, why must the urgent always take precedence over the important?
October 10, 2007 at 7:56 am
Yes you can use event notfications, and thanks for the link.
October 10, 2007 at 4:05 pm
Great article!! And thanks for the plug of my Blog. :smooooth:
October 10, 2007 at 4:37 pm
Thanks Patrick, There are many ways to this end. Yours.... simple....effective....to the point.
Awesome !!
CodeOn 😛
October 11, 2007 at 7:34 am
Thanks for such a great blog.
February 28, 2008 at 12:27 pm
When I cut and pasted the first procedure into a new query window and attempted to execute, I got a whole series of syntax errors:
Msg 102, Level 15, State 1, Procedure usp_DeadlockNotification, Line 5
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 28
Must declare the scalar variable "@Filter".
Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 31
Must declare the scalar variable "@Minutes".
Msg 156, Level 15, State 1, Procedure usp_DeadlockNotification, Line 38
Incorrect syntax near the keyword 'ELSE'.
February 29, 2008 at 7:29 am
Try this one:
IF OBJECT_ID( 'dbo.usp_DeadlockNotification') IS NOT NULL
DROP PROC dbo.usp_DeadlockNotification
GO
CREATE PROC dbo.usp_DeadlockNotification
@FilterBIT int = 0,
@Minutes INT = 30
AS
DECLARE @ErrorLog TABLE
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
DECLARE @Count INT,
@StartDate DATETIME,
@EndDate DATETIME
SET @Count = 0
SET NOCOUNT ON
-- Step I: Import Errorlog
INSERT INTO @Errorlog
EXEC xp_readerrorlog
---- Step II: How to search Errorlog
IF (@FilterBIT <> 0)
BEGIN
SELECT @EndDate = GETDATE()
SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)
SELECT @Count = COUNT(*)
FROM @Errorlog
WHERE LogDate BETWEEN @StartDate AND @EndDate
AND LogInfo LIKE '%Deadlock%'
END
ELSE
BEGIN
SELECT @Count = COUNT(*)
FROM @Errorlog
WHERE LogInfo LIKE '%Deadlock%'
END
---- Step III: Send Email
IF (@Count > 0)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLTestCluster',
@recipients = 'pleblanc@lamar.com',
@subject = 'Deadlocks',
@body = 'Please check errorlog for Deadlocks'
END
March 5, 2009 at 1:29 pm
Such complexity to handle something built-in and simple to configure? I concur... use alerts instead!
USE [msdb]
GO
--create "Operator" with customer's email
EXEC msdb.dbo.sp_add_operator @name=N'AppSupport',
@enabled=1,
@email_address=N'customer@emailthem.com',
@category_name=N'[Uncategorized]'
GO
--create deadlock alert
EXEC msdb.dbo.sp_add_alert @name=N'DeadLocks',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=5,
@include_event_description_in=1,
@notification_message=N'SQL Server deadlock alert.',
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
August 27, 2009 at 2:58 pm
hi is there a way to add the deadlock graph information from the log into your email notification without having to create an event?
August 31, 2009 at 9:27 am
Not without searching extensively through the error log. I would suggest using the Profiler or as you stated in your posts events, specifically extended events if you are running SQL 2008. If you would like to talk further about this please feel free to email me a pleblanc @tsqlscripts and I am sure we can figure something out.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply