Closing open sessions

Hi All
Is there a way/sript that i can schedule to close open sessions at a
certain time. I do have some sessions that are left open and doing nothing on
my db. I would like to check for the open sessions that are not doing
anything and also to close all sessions at 3am M-F. Thank you in advance.
You can use something like this. Just schedule it in Job Scheduler to run at
3am, and change the > 10 to be the number of minutes that you want to
consider a connection to be idle.
-- Kill all spids that havent had a batch for X number of minutes
DECLARE cur_KILLSPID CURSOR
READ_ONLY
FOR select spid from master..sysprocesses
where cmd = 'AWAITING COMMAND' and datediff(mi,last_batch, getdate()) > 10
DECLARE @.spid smallint, @.sql varchar(100)
OPEN cur_KILLSPID
FETCH NEXT FROM cur_KILLSPID INTO @.spid
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'KILL ' + cast(@.spid as varchar(10))
exec (@.sql)
DECLARE @.message varchar(100)
SELECT @.message = 'Killing SPID: ' + cast(@.spid as varchar(10))
PRINT @.message
END
FETCH NEXT FROM cur_KILLSPID INTO @.spid
END
CLOSE cur_KILLSPID
DEALLOCATE cur_KILLSPID
GO
AndyP,
Sr. Database Administrator,
MCDBA 2003,
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"MittyKom" wrote:

> Hi All
> Is there a way/sript that i can schedule to close open sessions at a
> certain time. I do have some sessions that are left open and doing nothing on
> my db. I would like to check for the open sessions that are not doing
> anything and also to close all sessions at 3am M-F. Thank you in advance.