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 sleeping. Show all posts
Showing posts with label sleeping. Show all posts
Tuesday, March 20, 2012
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...
connections[vbcol=seagreen]
hours.[vbcol=seagreen]
>
>.
>|||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...[vbcol=seagreen]
> 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
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.
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...
connections[vbcol=seagreen]
hours.[vbcol=seagreen]
>
>.
>|||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...[vbcol=seagreen]
> 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
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.
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
Jim
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
>
|||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[vbcol=seagreen]
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
connections[vbcol=seagreen]
hours.
>
>.
>
|||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...[vbcol=seagreen]
> 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
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.
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
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
>
|||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[vbcol=seagreen]
>news:351b01c48f4a$17717580$a601280a@.phx.gbl...
connections[vbcol=seagreen]
hours.
>
>.
>
|||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...[vbcol=seagreen]
> 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
>
> connection is open but not
> this state most of
> pooling...
> (PASS) and it's
> message
> connections
> hours.
Tuesday, February 14, 2012
Connection sleeping
If an MS SQL 2005 Connection is sleeping in the activity monitor, does it mean it's not closed? I'm pretty sure it's closed, just want to confirm.
Lately, our DB server got hammered with requests and it reached max-pool size (we implemented caching after that).
I have not found this to be true. I have a method which opens a connection, runs a SQL statement, then closes the connection. If I cal this method quickly, hundreds of times (such as in a loop), then I spawn hundreds of connections (which all appear to be sleeping) and the pool size is exceeded and everything locks up. There do not appear to be any connection leaks (the .Close() method is always called).
The very interesting thing is that this only happens in IIS -- it does not happen in Casini (the "built-in" ASP.NET server within Visual Studio). In Casini the connections seem to be handled much better -- this tells me it is not a SQL Server issue but may be an IIS connection pool handling issue.
Does anyone have any thoughts?
Thanks everyone,
Matt
Subscribe to:
Posts (Atom)