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)

--)