Kill Old Sessions

I've got a few databases which users access using Terminal Server. I
noticed today I had several (20+) sessions which had a Last Batch date
which were days even weeks old.
I want to kill these old sessions if the Last Batch date is greater
than 5 hours.
I also noticed there are several background sessions being run by the
sa account on master db and they are several days old. I don't believe
I should kill these sessions.
Does anyone have a script they currently use to manage these old
sessions?
Izzy
I forgot to list, I'm using SQL Server 2000.
Thanks,
Izzy wrote:
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy
|||It is just a matter of writing a cursor on the sysprocesses table. You can use
http://www.dbmaint.com/download/util...kill_users.sql as a starter for your script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160062917.135174.41640@.h48g2000cwc.googlegro ups.com...
>I forgot to list, I'm using SQL Server 2000.
> Thanks,
>
> Izzy wrote:
>
|||Execellent!
Thanks a bunch.
Izzy
Tibor Karaszi wrote:[vbcol=seagreen]
> It is just a matter of writing a cursor on the sysprocesses table. You can use
> http://www.dbmaint.com/download/util...kill_users.sql as a starter for your script.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160062917.135174.41640@.h48g2000cwc.googlegro ups.com...
|||It seems to me that you are treating the symptop, not the problem.
The symptom is the old sessions.
The problem is that people do not exit Terminal Server correctly. Can you
encourage users to log out of the Terminal Server correctly? Can you
remotely log the users out (and end their database connection in the
process)?
Keith Kratochvil
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160061806.706762.53740@.m7g2000cwm.googlegrou ps.com...
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy
>
|||That is exactly the problem, most users are set up to be logged out of
terminal server at midnight if they are not already logged out.
BUT, I have users who work in our shop on 3rd shift who use the same
account as users on first shift.
I've explained too them they need to log out correctly, but of course
users do whatever they want anyway, and just give you lip service while
your in front of them.
Question:
In the example you sent, your query does not eliminate some sessions
from being killed. For instance, I have 4 which have this listed in the
"cmd" line:
LAZY WRITER
LOG WRITER
LOCK MONITOR
CHECKPOINT SLEEP
Is there going to be any negative or unexpected behavior if these get
killed?
Is there something I should query on to eliminate system processes?
Izzy
Keith Kratochvil wrote:[vbcol=seagreen]
> It seems to me that you are treating the symptop, not the problem.
> The symptom is the old sessions.
> The problem is that people do not exit Terminal Server correctly. Can you
> encourage users to log out of the Terminal Server correctly? Can you
> remotely log the users out (and end their database connection in the
> process)?
> --
> Keith Kratochvil
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160061806.706762.53740@.m7g2000cwm.googlegrou ps.com...
|||> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
These are system connections, and I'm pretty certain they can't be killed even if you try to (else
MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
statement, like spid > 50.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160066738.477635.116660@.h48g2000cwc.googlegr oups.com...
> That is exactly the problem, most users are set up to be logged out of
> terminal server at midnight if they are not already logged out.
> BUT, I have users who work in our shop on 3rd shift who use the same
> account as users on first shift.
> I've explained too them they need to log out correctly, but of course
> users do whatever they want anyway, and just give you lip service while
> your in front of them.
> Question:
> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
> Is there something I should query on to eliminate system processes?
> Izzy
>
> Keith Kratochvil wrote:
>
|||You've been very helpful Tibor, many thanks!
Izzy
Tibor Karaszi wrote:[vbcol=seagreen]
> These are system connections, and I'm pretty certain they can't be killed even if you try to (else
> MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
> statement, like spid > 50.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160066738.477635.116660@.h48g2000cwc.googlegr oups.com...