You probably have users that like to connect the database, run a massive query, and then go home for the evening locking your nightly maintenance jobs and all other users out. I put together a handy little script to kill those offending users by looking at the last activity and killing those spids that are sitting idle.
USE [DB_UTILS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*----------------------------------------------------------------
Description:
Kills connections greater than xx days old in specific database and/or from specific program.
----------------------------------------------------------------
Called By:
DB_UTILS - sp_KillOldConnections
----------------------------------------------------------------
Modifications: 7/2/14 Todd Palecek Initial Build
8/1/14 Todd Palecek Changed to .04167 days (1 hour) old to kill
----------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[sp_KillOldConnections] AS
BEGIN
CREATE TABLE #TEMP_WHO2
(SPID int,
Status char(20),
Login char(30),
HostName char(30),
BlkBy char(5),
DBName char(20),
Command char(75),
CPUTime int,
DiskIO int,
LastBatch char(20),
ProgramName char(75),
SPID2 int,
REQUESTID int) --not present in SQL 2000 or 7
INSERT INTO #TEMP_WHO2 EXEC sp_who2
--calculate year to add to month/day/time from LastBatch
declare @date varchar(10)
set @date = convert(varchar,datepart(yy,getdate()))
DECLARE @SPID varchar(50)
DECLARE cursorName CURSOR FOR
Select Spid
FROM #TEMP_WHO2
WHERE DBName='XXXXX'
--and ProgramName like 'XXXXX%' and
and convert (datetime ,@date + '/' + LastBatch) < getdate() -.04167 and spid > 50
ORDER BY SPID
OPEN cursorName
FETCH NEXT FROM cursorName into @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
print @SPID
declare @cmd varchar(20)
set @cmd = 'kill ' + @SPID
print @cmd
exec (@cmd)
FETCH NEXT FROM cursorName
INTO @SPID
END
CLOSE cursorName
DEALLOCATE cursorName
DROP TABLE #TEMP_WHO2
END
GO
