May 30, 2006 at 3:25 pm
Is there any timeout parameter for Sqlserver logins for the whole instance or for a specific database that will disconnect a session if it has been idle for a certain amount of time?
May 30, 2006 at 3:43 pm
I found the following, however I do not believe this applies to what I am looking for:
Login time-out (seconds)
Specify the number of seconds to wait before the instance returns from a failed remote login attempt.
Query time-out (seconds)
Specify the number of seconds that must elapse during the processing of a remote query before the query times out. The default is 0, which allows an infinite wait.
May 31, 2006 at 8:50 am
This is best handled by the application that opens the connection. However, if you are using Query Analyzer, for example, then that is not possible.
You could try running a query (like the one below) as a scheduled job. The account used to run the job must be a member of the sysadmin or processadmin server roles to execute the KILL statement.
DECLARE @spid int
, @spidC varchar(5)
SET @spid = 0
WHILE @spid IS NOT NULL
BEGIN
SELECT @spid = Min(spid)
FROM sysprocesses
WHERE open_tran = 0
AND spid >= 50
AND spid > @spid
AND cmd = 'AWAITING COMMAND'
AND last_batch < DateAdd(mi, -5, Getdate()) -- 5 minutes
IF @spid IS NOT NULL
BEGIN
SET @spidC = Convert(varchar(5), @spid)
PRINT 'Killing SPID ' + @spidC
EXEC ('KILL ' + @spidC)
END --IF
END --WHILE
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply