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
Showing posts with label kill. Show all posts
Showing posts with label kill. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
Connections
I've got a satellite group that acts as their own dba's on
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.
Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.
|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic
>
>.
>
|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.
Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.
|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic
>
>.
>
|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
Connections
I've got a satellite group that acts as their own dba's on
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic[vbcol=seagreen]
>
>.
>|||Sometimes you get "ghost connections". There are some reasons this can happe
n, the connection
executing an extended stored procedure is one such possibility. There can be
other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it
will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel
certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
>
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
on[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
magic[vbcol=seagreen]
>
>.
>|||Sometimes you get "ghost connections". There are some reasons this can happe
n, the connection
executing an extended stored procedure is one such possibility. There can be
other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it
will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel
certain you have waited
long enough, recycling the SQL Server is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx but from what they're saying, it was definitely a user
> process.
>
> (typically spid>50) that
> what they're seeing.
> message
> on
> the
> all
> magic
Connections
I've got a satellite group that acts as their own dba's on
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
>> I've got a satellite group that acts as their own dba's
on
>> a 7.0 server. They claim that they cannot kill certain
>> connections, i.e. they typed in kill and nothing
>> happened. I have seen that happen with web-based
>> connection pooling apps but not with a regular app that
>> they're working with.
>> Anyway, what I wanted to ask is: is the kill command
the
>> only way to kill a connection besides the winner-take-
all
>> methods of taking the database offline or stopping and
>> restarting services? Just wondering if there's some
magic
>> bullet that I hadn't heard of.
>
>.
>|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...
> Thx but from what they're saying, it was definitely a user
> process.
> >--Original Message--
> >Kill can only be used to terminate user processes
> (typically spid>50) that
> >is not executing an extended procedure. Perhaps, that's
> what they're seeing.
> >
> >
> >"NTel" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> >> I've got a satellite group that acts as their own dba's
> on
> >> a 7.0 server. They claim that they cannot kill certain
> >> connections, i.e. they typed in kill and nothing
> >> happened. I have seen that happen with web-based
> >> connection pooling apps but not with a regular app that
> >> they're working with.
> >>
> >> Anyway, what I wanted to ask is: is the kill command
> the
> >> only way to kill a connection besides the winner-take-
> all
> >> methods of taking the database offline or stopping and
> >> restarting services? Just wondering if there's some
> magic
> >> bullet that I hadn't heard of.
> >
> >
> >.
> >
a 7.0 server. They claim that they cannot kill certain
connections, i.e. they typed in kill and nothing
happened. I have seen that happen with web-based
connection pooling apps but not with a regular app that
they're working with.
Anyway, what I wanted to ask is: is the kill command the
only way to kill a connection besides the winner-take-all
methods of taking the database offline or stopping and
restarting services? Just wondering if there's some magic
bullet that I hadn't heard of.Kill can only be used to terminate user processes (typically spid>50) that
is not executing an extended procedure. Perhaps, that's what they're seeing.
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> I've got a satellite group that acts as their own dba's on
> a 7.0 server. They claim that they cannot kill certain
> connections, i.e. they typed in kill and nothing
> happened. I have seen that happen with web-based
> connection pooling apps but not with a regular app that
> they're working with.
> Anyway, what I wanted to ask is: is the kill command the
> only way to kill a connection besides the winner-take-all
> methods of taking the database offline or stopping and
> restarting services? Just wondering if there's some magic
> bullet that I hadn't heard of.|||Thx but from what they're saying, it was definitely a user
process.
>--Original Message--
>Kill can only be used to terminate user processes
(typically spid>50) that
>is not executing an extended procedure. Perhaps, that's
what they're seeing.
>
>"NTel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
>> I've got a satellite group that acts as their own dba's
on
>> a 7.0 server. They claim that they cannot kill certain
>> connections, i.e. they typed in kill and nothing
>> happened. I have seen that happen with web-based
>> connection pooling apps but not with a regular app that
>> they're working with.
>> Anyway, what I wanted to ask is: is the kill command
the
>> only way to kill a connection besides the winner-take-
all
>> methods of taking the database offline or stopping and
>> restarting services? Just wondering if there's some
magic
>> bullet that I hadn't heard of.
>
>.
>|||Sometimes you get "ghost connections". There are some reasons this can happen, the connection
executing an extended stored procedure is one such possibility. There can be other things as well,
but this has been getting better with versions and service packs.
Note that the connection might be in the middle of a large rollback, and it will not die until the
rollback is done.
No magic bullets here. Wait for a possible rollback to complete, if you feel certain you have waited
long enough, recycling the SQL Server is the way to go.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NTel" <anonymous@.discussions.microsoft.com> wrote in message
news:3c8901c48fae$fcf54370$a301280a@.phx.gbl...
> Thx but from what they're saying, it was definitely a user
> process.
> >--Original Message--
> >Kill can only be used to terminate user processes
> (typically spid>50) that
> >is not executing an extended procedure. Perhaps, that's
> what they're seeing.
> >
> >
> >"NTel" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:3bb101c48fa2$07107710$a601280a@.phx.gbl...
> >> I've got a satellite group that acts as their own dba's
> on
> >> a 7.0 server. They claim that they cannot kill certain
> >> connections, i.e. they typed in kill and nothing
> >> happened. I have seen that happen with web-based
> >> connection pooling apps but not with a regular app that
> >> they're working with.
> >>
> >> Anyway, what I wanted to ask is: is the kill command
> the
> >> only way to kill a connection besides the winner-take-
> all
> >> methods of taking the database offline or stopping and
> >> restarting services? Just wondering if there's some
> magic
> >> bullet that I hadn't heard of.
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)