As a refinement to this method you could try

insert into #sysprocs
Select * from sysprosesses (nolock)

then...

select * from #sysprocs where waittime >@waittime and program_name =
'PeopleSoft-'

Less contention on sysprocesses this way. This is similar to how sp_who
works


-----Original Message-----
From: Sherman, Bill [mailto:[Email Address Removed] Thursday, May 29, 2003 11:17 AM
To: LazyDBA.com Discussion
Subject: RE: Auto alert for locking.....


--POLLDEADLOCKS

declare @i int
declare @poll int
declare @polling_intrv char(8)
declare @waittime int

set @i = 1
set @poll = 100
set @polling_intrv = '00:00:05'
set @waittime = 3000
You may be able to mod this...

use master
while @i <@poll
BEGIN
waitfor delay @polling_intrv
select * from sysprocesses where waittime >@waittime and program_name =
'PeopleSoft-'
set @i = @i + 1
end
print 'done'

-----Original Message-----
From: anthony.[Email Address Removed] [mailto:anthony.[Email Address Removed] Wednesday, May 28, 2003 4:17 PM
To: LazyDBA.com Discussion
Subject: Auto alert for locking.....


Guys.

I am after an automatic way to trigger an alert if there is an excess
amount of locking in the database..

Is there a sp_ or some sort of monitor I can create to do this?

Just briefly I can think of having a stored proc that queries the same
fields sp_lock does, at 30sec intervals..

Is there a better way?


Thanks for your help.
__________________________________________
Anthony Hand
DBA | Systems Integration
Aon Risk Services Australia Ltd
Ph: +61 2 9253 7516
Fax: +61 2 92537952
Mob: +61 415 877 988



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page