use master
go

if exists (select 'SP exists' from sysobjects where name = 'Up_KillDatabaseLongUsers' and xtype = 'p')
	drop proc Up_KillDatabaseLongUsers
go
set nocount on
set quoted_identifier off
go
create procedure Up_KillDatabaseLongUsers (@Duration	int=100, @DBName	varchar(255))
as
--	Create By:	Regan Galbraith
--	Create On:	2004-07-20
--	Purpose:	
--
--		This stored procedure was written to facilitate the automatic cleanign down of lost/disconnected spids.
--		It works through a database, killing all connected spids that are not its own spid, and have been connected longer 
--		than the specified duration.
--
--	Example:
--		exec Up_KillDatabaseLongUsers 1000000,'Master'
--
--	Possible future additions:
--		logging work done to a table, net send person killed,error handling
--
--	Change Control:	version 1 - 	creation and adding of comment
--			v1.1 - 		change 2004-07-26 - Regan Galbraith
--					added [ ] to cater for dbs named with numerics or reserved words
--			v1.2 - 		change 2006-03-15 - Regan Galbraith
--					modified base sp to serve as example for web query
--

BEGIN
set nocount on
declare @DurationStr	varchar(25)
set	@DurationStr = str(@Duration)
exec ('use [' +  @DBname + '] 

declare @spid varchar(9)
declare @KillNumber	int
set	@KillNumber = 0
	
DECLARE spid_cursor CURSOR FOR select ltrim(rtrim(str(spid))) 
from master..sysprocesses 
	where 	dbid in (select dbid from master..sysdatabases where name = '''+@DBname+''')
	and	spid <> @@spid
	and	datediff(second,last_batch,getdate()) between ' +@DurationStr+ ' and 0
	and	loginame not in (''sa'')		--UserId exclusion list

OPEN spid_cursor
FETCH NEXT FROM spid_cursor INTO @spid
	
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	
	declare @sql varchar (100)
	
	select @sql=''kill ''+@spid
	print @sql
	exec (@sql)
	set @KillNumber = @KillNumber + 1
	FETCH NEXT FROM spid_cursor INTO @spid
	
	END 
	close spid_cursor
	DEALLOCATE spid_cursor

select 	@KillNumber as [Number of Spids killed]')

END
go
exec Up_KillDatabaseLongUsers 1000000,'Master'