DISCLAIMER: I am not condoning this, merely providing information to consider possibilities. This is not supported in any way.
First is a table that is used as an audit log table.
/*
Use this table to add UserID values from tsmUser that you DO NOT WANT to get disconnected
when running the sp_KillUsers_RKL procedure. If a record exists in this table it will not be Killed.
Fill the UserID value in this table with the UserID value from tsmUser.
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsmUserDoNotKill_RKL]') AND type in (N'U'))
DROP TABLE [dbo].[tsmUserDoNotKill_RKL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tsmUserDoNotKill_RKL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tsmUserDoNotKill_RKL](
[UserID] [varchar](30) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
Then you have the stored procedure which is used to execute the process. You call the stored procedure and pass in the database name and an integer value which respresents minutes. Read the code and the comments below carefully. You can see how to call the SP in the code. You can also do some selections out of your database to see what it is going to do before you actually run this process.
USE [mas500_app]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- Procedure code to kill open sessions to free them up for other users.
Find out database name to pass in
select * from sys.databases
--Example of a procedure execution:
USE master
GO
exec sp_KillUsers_RKL 'mas500_app'
GO
*/
CREATE PROCEDURE [dbo].[sp_KillUsers_RKL] (@dbName varchar(50), @killmin int)
AS
BEGIN
DECLARE @currSpid int
DECLARE @SQLstmt varchar(100)
/*
You can use this code to look at the results and other related information
DECLARE @dbname varchar(50)
SET @dbname = 'mas500_app'
SELECT dmsess.login_name, dmsess.nt_user_name, dmsess.session_id, dmsess.status,
dmsess.host_name, dmsess.host_process_id,
dmsess.context_info, dmsess.cpu_time, dmsess.is_user_process,
dmsess.last_request_end_time, dmsess.program_name, dmsess.total_elapsed_time,
dmsess.memory_usage, dmsess.reads,
cast(getdate() as money) as nowtime, CAST(dmsess.last_request_end_time as money) as lrettime,
cast(getdate() as money) - CAST(dmsess.last_request_end_time as money) as timediff,
dmreq.command, dmreq.connection_id,
dmconn.connect_time, dmconn.last_read, dmconn.last_write,
tsmuser.EMailAddr, tsmuser.UserName, tsmuser.dbuserid,
sysdb.name
FROM sys.dm_exec_sessions dmsess
INNER JOIN sys.dm_exec_requests dmreq
ON dmreq.session_id = dmsess.session_id
INNER JOIN sys.dm_exec_connections dmconn
ON dmconn.connection_id = dmreq.connection_id
INNER JOIN sys.databases sysdb
ON sysdb.database_id = dmreq.database_id
INNER JOIN tsmUser
ON tsmuser.UserID = dmsess.login_name
WHERE cast(getdate() as money) - CAST(dmsess.last_request_end_time as money) > .5 -- half day
and sysdb.name = @dbname
*/
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tmpKillSession]') AND type in (N'U'))
DROP TABLE [dbo].[#tmpKillSession]
SELECT dmsess.session_id, dmsess.login_name,
dmsess.last_request_end_time,
datediff(mi,'1970-01-01',getdate()) as getdatewholemins,
datediff(mi,'1970-01-01',dmsess.last_request_end_time) as lastdatewholemins,
datediff(mi,'1970-01-01',getdate()) -
datediff(mi,'1970-01-01',dmsess.last_request_end_time) as wholemins,
OverMax = CASE
WHEN (datediff(mi,'1970-01-01',getdate()) - datediff(mi,'1970-01-01',dmsess.last_request_end_time)) > @killmin
THEN 'OVER'
ELSE 'UNDR'
END
INTO #tmpKillSession
FROM sys.dm_exec_sessions dmsess
INNER JOIN tsmUser
ON tsmuser.IntegratedSecurityAcct = dmsess.login_name
WHERE dmsess.login_name IN (
SELECT dmsess.login_name
FROM sys.dm_exec_sessions dmsess
INNER JOIN tsmUser
ON tsmuser.IntegratedSecurityAcct = dmsess.login_name
WHERE datediff(mi,'1970-01-01',getdate()) - datediff(mi,'1970-01-01',dmsess.last_request_end_time) > @killmin
)
DECLARE UsersInsideDb
CURSOR FOR SELECT session_id
FROM #tmpKillSession
WHERE login_name NOT IN (SELECT login_name FROM #tmpKillSession WHERE overmax = 'UNDR')
AND UserID NOT IN (SELECT UserID FROM tsmUserDoNotKill_RKL)
/*
CURSOR FOR SELECT dmsess.session_id
FROM sys.dm_exec_sessions dmsess
INNER JOIN sys.dm_exec_requests dmreq
ON dmreq.session_id = dmsess.session_id
INNER JOIN sys.dm_exec_connections dmconn
ON dmconn.connection_id = dmreq.connection_id
INNER JOIN sys.databases sysdb
ON sysdb.database_id = dmreq.database_id
INNER JOIN tsmUser
ON tsmuser.UserID = dmsess.login_name
WHERE sysdb.name = @dbname
and cast(getdate() as money) - CAST(dmsess.last_request_end_time as money) > .5
and tsmUser.UserID <> 'admin'
*/
OPEN UsersInsideDb
FETCH NEXT FROM UsersInsideDb INTO @currSpid
while @@fetch_status = 0
BEGIN
SET @SQLstmt = 'kill '+ convert(char(5),@currSpid)
PRINT @SQLstmt
EXEC (@SQLstmt)
FETCH NEXT FROM UsersInsideDb INTO @currSpid
END
CLOSE UsersInsideDb
DEALLOCATE UsersInsideDb
END
GO
Joe Noll
RKL eSolutions LLC
http://www.rklesolutions.com
