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 o
n
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.The sp_who procedure will return all open connections and their current
status. The KILL command can be used to terminate a process. However, just
because a process is idle at the moment, does not mean that it is not in use
by an application.
There is a statement to set the database to single user mode and first close
all processes while rolling back open transactions:
alter database <dbname> set single_user with rollback immediate
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:486EA53E-8CD9-498E-8E50-34FCD18C95B5@.microsoft.com...
> 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.
>

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 o
n
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.

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.

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.

Closing all open connections/sessions

Hi all
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:

> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.