use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_blockingalert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_blockingalert]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


Create procedure usp_blockingalert 
@thresholdmin int = 1,
@applicationlog varchar(1000) = 'd:\BLOCKING.LOG'
--Created by MAK
--Created date: Oct 14, 2004
--Objective: To run BlockMonitor Job when blocking is more than a minute
as
declare @count1 int
declare @runstatus int
declare @date varchar(100)
declare @error varchar(1000)

select @count1 =count(*) from 
master..BlockLastBatch a,
(select spid,blocked,last_batch from master..sysprocesses (nolock) where blocked<>0) as b
where a.last_batch = b.last_batch

if @count1 > 0
begin
set @error = 'Blocking persist more than ' + convert(varchar(10),@thresholdmin )
exec master..usp_writetofile @applicationlog , @error
--print 'Blocking more than one minute '
--select @runstatus =run_status from msdb..sysjobhistory (nolock) where job_id =
--(select job_id from msdb..sysjobs (nolock) where name ='Job_BlockMonitor')

--if @runstatus <>4 
--begin
exec msdb..sp_start_job 'Job_BlockMonitor'
--end
end

truncate table master..BlockLastBatch 
insert into master..BlockLastBatch 
select spid,blocked,last_batch from master..sysprocesses (nolock) where blocked<>0




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO