How can I setup SQLServer 2000 to automatically disconnect clients that have
been inactive for 2 hours? There was an option to do this in Sybase
SQLAnywhere, but I haven't found it SQLServer.
Thanks.
Hi
Nothing in SQL. If you need to do this, you need to write code that call the
KILL function.
The system stored procedure sp_who2 is a good code base to use.
Regards
Mike
"Daryl A." wrote:
> How can I setup SQLServer 2000 to automatically disconnect clients that have
> been inactive for 2 hours? There was an option to do this in Sybase
> SQLAnywhere, but I haven't found it SQLServer.
> Thanks.
Subscribe to:
Post Comments (Atom)
1 comment:
I used system view dm_exec_connections:
SELECT session_id, CASE WHEN last_read>last_write THEN last_read ELSE last_write END as [last_activity] INTO #conn FROM sys.dm_exec_connections
ALTER TABLE #conn ADD ID int IDENTITY(1,1) NOT NULL
DECLARE @conn_count int;
SELECT @conn_count = COUNT(*) FROM #conn
DECLARE @actual_conn int = 1;
WHILE (@actual_conn<=@conn_count)
BEGIN
DECLARE @actual_conn_session_id int
DECLARE @actual_conn_last_activity datetime
SELECT @actual_conn_last_activity=last_activity, @actual_conn_session_id=session_id FROM #conn WHERE ID = @actual_conn
IF(@actual_conn_last_activity < DATEADD(HOUR, -2, GETDATE()))
BEGIN
--SELECT @actual_conn_session_id, @actual_conn_last_activity
DECLARE @KillSql nvarchar(500) = N'KILL ' + CAST(@actual_conn_session_id AS nvarchar)
EXEC sp_executesql @KillSql
END
SET @actual_conn = @actual_conn + 1
END
DROP TABLE #conn
Post a Comment