Connections...

Dear All,
We are getting a lot of sleeping (and orphan) connections
to our database, so I would like to create a script to
kill them based upon them being sleeping for over 2 hours.
Can anyone point me to the table that holds this info ?
I don't want the script as I will be trying that myself.
TIA
JimThe table is master.dbo.sysprocesses...
Sleeping is not bad... It simply means that the connection is open but not
currently doing any work... Most connections will be in this state most of
the time. Also consider the effect of connection pooling...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> Dear All,
> We are getting a lot of sleeping (and orphan) connections
> to our database, so I would like to create a script to
> kill them based upon them being sleeping for over 2 hours.
> Can anyone point me to the table that holds this info ?
> I don't want the script as I will be trying that myself.
> TIA
> Jim
>|||Thanks Wayne,
In our case its usually because the data objects have made
a new connection, so what we have is a lot of unused
connections by the same person. Its those it want to get
rid of.
Thanks
Jim
>--Original Message--
>The table is master.dbo.sysprocesses...
>Sleeping is not bad... It simply means that the
connection is open but not
>currently doing any work... Most connections will be in
this state most of
>the time. Also consider the effect of connection
pooling...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
>> Dear All,
>> We are getting a lot of sleeping (and orphan)
connections
>> to our database, so I would like to create a script to
>> kill them based upon them being sleeping for over 2
hours.
>> Can anyone point me to the table that holds this info ?
>> I don't want the script as I will be trying that myself.
>> TIA
>> Jim
>>
>
>.
>|||If that's the case, either your programmers aren't properly cleaning up the
connections, or these connections are still in use by the data objects and
you'll cause headaches for your developers if you take this path.
Personally, I'd make them perform a code review and fix the problem - most
connection leaks are due to sloppy coding practices.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:376e01c48f66$403ea520$a301280a@.phx.gbl...
> Thanks Wayne,
> In our case its usually because the data objects have made
> a new connection, so what we have is a lot of unused
> connections by the same person. Its those it want to get
> rid of.
> Thanks
> Jim
>
> >--Original Message--
> >The table is master.dbo.sysprocesses...
> >
> >Sleeping is not bad... It simply means that the
> connection is open but not
> >currently doing any work... Most connections will be in
> this state most of
> >the time. Also consider the effect of connection
> pooling...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Mariner, Charlotte, NC
> >www.mariner-usa.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:351b01c48f4a$17717580$a601280a@.phx.gbl...
> >> Dear All,
> >> We are getting a lot of sleeping (and orphan)
> connections
> >> to our database, so I would like to create a script to
> >> kill them based upon them being sleeping for over 2
> hours.
> >>
> >> Can anyone point me to the table that holds this info ?
> >>
> >> I don't want the script as I will be trying that myself.
> >>
> >> TIA
> >> Jim
> >>
> >>
> >
> >
> >.
> >sqlsql