1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| CREATE PROCEDURE AutoKiller AS
declare @dlimite datetime
set @dlimite = dateadd(s,-30,getdate()) -- requete de plus de 30s
Declare @spid int
Declare @blocked int
Declare @Connexion datetime
Declare @Execution datetime
set @nomServeur = 'ALPHEEDBP03'
select TOP 1 @spid=spid,
@Execution=last_batch,
@Connexion=login_time,
@blocked=blocked
from master.dbo.sysprocesses with (nolock)
where spid in (
select req_spid
from master.dbo.syslockinfo with (nolock)
where rsc_objid in (
select id from master.dbo.syslocks with (nolock)
where id>0)
AND req_status=1
AND rsc_objid>0
AND db_name(rsc_dbid) = '<NomBaseASurveiller>'
)
and @dlimite>last_batch
order by last_batch
if @@rowcount>0
BEGIN
WAITFOR DELAY '000:00:01'
IF @spid in (select spid from master.dbo.sysprocesses with (nolock)
where spid in (select spid from master.dbo.syslocks with (nolock) where id>0)
AND db_name(dbid) = '<nomBaseASurveiller>'
AND @dlimite>last_batch)
BEGIN
EXEC ('KILL ' + @spid )
END
END
GO |
Partager