May not be a SQL question but you may have the answer..

Every once in a while our SQL Servers hiccup or take a blip in the form of
blocking,etc.. and when this happens our Web and Application servers begin
to misbehave and our system engineers begin to reboot these servers to
resolve the issue. They do not have a solid answer, but I think these
servers try to open up more connections and probably consume all threads and
memory( not too sure, just speculating)..
Have you seen this kind of behavior and if so, why is it per your
environment and what have you done to avoid those reboots by making the Web
and app servers smart to know what to do when there are SQL failures? Its
one thing to have SQL conk off which is costly, but then we spend another
good portion of our time rebooting every other non SQL Server too. Sounds
like some bad way of programming but I want to take your inputs to my
programmers/architects.
Thank you
>From your question, i assume that you will never restart SQL Server
services, and only webserver and app's, and if thats the case i have a
strong feeling that SQL Server is not causing the issue. I had seen in
very rare scenerios where SQL will use all the 255 (Default) worker
threads, and if its with memory, by restarting other services, SQL
will not release its memory. Since you are speculating, this info is
just for your understanding.
Also you might want to collect more symptoms as to what is happening
on SQL when you are seeing the issue, like CPU spike, from
sysprocesses check and see if any extensive blocking (though these
might not be related, but getting all symptoms will always give you
answers )
|||Dinu. thanks for the response.
I do know whats happening to SQL, but the problem exacerbates to where the
web and app servers suffer and need to be restarted and want to know how can
we make the app servers smart
<dinu_babu@.hotmail.com> wrote in message
news:1185077813.592904.45890@.e9g2000prf.googlegrou ps.com...
> services, and only webserver and app's, and if thats the case i have a
> strong feeling that SQL Server is not causing the issue. I had seen in
> very rare scenerios where SQL will use all the 255 (Default) worker
> threads, and if its with memory, by restarting other services, SQL
> will not release its memory. Since you are speculating, this info is
> just for your understanding.
> Also you might want to collect more symptoms as to what is happening
> on SQL when you are seeing the issue, like CPU spike, from
> sysprocesses check and see if any extensive blocking (though these
> might not be related, but getting all symptoms will always give you
> answers )
>
|||You are probably on the right track with your speculation. Connections and
commands take longer when there's a network or SQL problem. Without a
governor mechanism in place, client requests can accumulate to the point of
resource starvation and an application restart or server reboot is often the
easiest and fastest way to get things running again.
The effect of network/SQL issues can be mitigated by setting a limit on
concurrent application requests. This assumes applications are coded in
such a way as to be resilient following a problem. For example, a
middle-tier app with a persistent database connections will need to be smart
enough to gracefully retry database connections instead of requiring a
restart. Exception handling needs to be especially thorough and tested
accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Every once in a while our SQL Servers hiccup or take a blip in the form of
> blocking,etc.. and when this happens our Web and Application servers begin
> to misbehave and our system engineers begin to reboot these servers to
> resolve the issue. They do not have a solid answer, but I think these
> servers try to open up more connections and probably consume all threads
> and memory( not too sure, just speculating)..
> Have you seen this kind of behavior and if so, why is it per your
> environment and what have you done to avoid those reboots by making the
> Web and app servers smart to know what to do when there are SQL failures?
> Its one thing to have SQL conk off which is costly, but then we spend
> another good portion of our time rebooting every other non SQL Server too.
> Sounds like some bad way of programming but I want to take your inputs to
> my programmers/architects.
> Thank you
>
|||This is what I was looking for to hear and the more I can obtain knowledge
on this area, the better.
Is there any whitepaper or anything on this subject that I can forward to
the devs. Basically they need to know how to make the apps more resilient
and have the right exception handling.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
> You are probably on the right track with your speculation. Connections
> and commands take longer when there's a network or SQL problem. Without a
> governor mechanism in place, client requests can accumulate to the point
> of resource starvation and an application restart or server reboot is
> often the easiest and fastest way to get things running again.
> The effect of network/SQL issues can be mitigated by setting a limit on
> concurrent application requests. This assumes applications are coded in
> such a way as to be resilient following a problem. For example, a
> middle-tier app with a persistent database connections will need to be
> smart enough to gracefully retry database connections instead of requiring
> a restart. Exception handling needs to be especially thorough and tested
> accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
Here's an article that discusses database mirroring failover retry but the
same retry pattern can be used in general:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
There are many articles on exception handling best practices. Here are a
couple recent ones for .Net.
http://www.ftponline.com/vsm/2007_06/magazine/columns/csharp/default.aspx
http://www.codeproject.com/dotnet/exceptionbestpractices.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uUA94ZJzHHA.748@.TK2MSFTNGP04.phx.gbl...
> This is what I was looking for to hear and the more I can obtain knowledge
> on this area, the better.
> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
>
|||On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Every once in a while our SQL Servers hiccup or take a blip in the form of
>blocking,etc.. and when this happens our Web and Application servers begin
>to misbehave and our system engineers begin to reboot these servers to
>resolve the issue.
If SQL Server connections are being blocked, as shown by EXEC sp_who,
rebooting is an extreme response. The first thing to check for is a
long running task that is holding locks. If there is one connection
doing the blocking then KILL is quicker and more selective than a
reboot. Of course it is best to figure out what that connection is
doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
Roy Harvey
Beacon Falls, CT
|||Roy,
I meant we reboot our web and application servers and not out SQL Servers.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:hr89a3drmd47bfsvqhn0kkhf13ar92dvto@.4ax.com...
> On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> If SQL Server connections are being blocked, as shown by EXEC sp_who,
> rebooting is an extreme response. The first thing to check for is a
> long running task that is holding locks. If there is one connection
> doing the blocking then KILL is quicker and more selective than a
> reboot. Of course it is best to figure out what that connection is
> doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
> Roy Harvey
> Beacon Falls, CT
|||On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Roy,
>I meant we reboot our web and application servers and not out SQL Servers.
So that drops all the connections from the client side. It still
seems like it would be worth looking at KILLing a specific connection
rather than taking the application off line long enough for a reboot.
Of course it requires much more knowledge.
Roy Harvey
Beacon Falls, CT
|||Our web and app servers go bonkers and thats what I am trying to figure..
why they do so when there is a small blip on the SQL server ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:iop9a3hb9ca2ql3a6hgh3dasp20p33duqf@.4ax.com...
> On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> So that drops all the connections from the client side. It still
> seems like it would be worth looking at KILLing a specific connection
> rather than taking the application off line long enough for a reboot.
> Of course it requires much more knowledge.
> Roy Harvey
> Beacon Falls, CT

May not be a SQL question but you may have the answer..

Every once in a while our SQL Servers hiccup or take a blip in the form of
blocking,etc.. and when this happens our Web and Application servers begin
to misbehave and our system engineers begin to reboot these servers to
resolve the issue. They do not have a solid answer, but I think these
servers try to open up more connections and probably consume all threads and
memory( not too sure, just speculating)..
Have you seen this kind of behavior and if so, why is it per your
environment and what have you done to avoid those reboots by making the Web
and app servers smart to know what to do when there are SQL failures? Its
one thing to have SQL conk off which is costly, but then we spend another
good portion of our time rebooting every other non SQL Server too. Sounds
like some bad way of programming but I want to take your inputs to my
programmers/architects.
Thank you>From your question, i assume that you will never restart SQL Server
services, and only webserver and app's, and if thats the case i have a
strong feeling that SQL Server is not causing the issue. I had seen in
very rare scenerios where SQL will use all the 255 (Default) worker
threads, and if its with memory, by restarting other services, SQL
will not release its memory. Since you are speculating, this info is
just for your understanding.
Also you might want to collect more symptoms as to what is happening
on SQL when you are seeing the issue, like CPU spike, from
sysprocesses check and see if any extensive blocking (though these
might not be related, but getting all symptoms will always give you
answers )|||Dinu. thanks for the response.
I do know whats happening to SQL, but the problem exacerbates to where the
web and app servers suffer and need to be restarted and want to know how can
we make the app servers smart
<dinu_babu@.hotmail.com> wrote in message
news:1185077813.592904.45890@.e9g2000prf.googlegroups.com...
> services, and only webserver and app's, and if thats the case i have a
> strong feeling that SQL Server is not causing the issue. I had seen in
> very rare scenerios where SQL will use all the 255 (Default) worker
> threads, and if its with memory, by restarting other services, SQL
> will not release its memory. Since you are speculating, this info is
> just for your understanding.
> Also you might want to collect more symptoms as to what is happening
> on SQL when you are seeing the issue, like CPU spike, from
> sysprocesses check and see if any extensive blocking (though these
> might not be related, but getting all symptoms will always give you
> answers )
>|||You are probably on the right track with your speculation. Connections and
commands take longer when there's a network or SQL problem. Without a
governor mechanism in place, client requests can accumulate to the point of
resource starvation and an application restart or server reboot is often the
easiest and fastest way to get things running again.
The effect of network/SQL issues can be mitigated by setting a limit on
concurrent application requests. This assumes applications are coded in
such a way as to be resilient following a problem. For example, a
middle-tier app with a persistent database connections will need to be smart
enough to gracefully retry database connections instead of requiring a
restart. Exception handling needs to be especially thorough and tested
accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Every once in a while our SQL Servers hiccup or take a blip in the form of
> blocking,etc.. and when this happens our Web and Application servers begin
> to misbehave and our system engineers begin to reboot these servers to
> resolve the issue. They do not have a solid answer, but I think these
> servers try to open up more connections and probably consume all threads
> and memory( not too sure, just speculating)..
> Have you seen this kind of behavior and if so, why is it per your
> environment and what have you done to avoid those reboots by making the
> Web and app servers smart to know what to do when there are SQL failures?
> Its one thing to have SQL conk off which is costly, but then we spend
> another good portion of our time rebooting every other non SQL Server too.
> Sounds like some bad way of programming but I want to take your inputs to
> my programmers/architects.
> Thank you
>|||This is what I was looking for to hear and the more I can obtain knowledge
on this area, the better.
Is there any whitepaper or anything on this subject that I can forward to
the devs. Basically they need to know how to make the apps more resilient
and have the right exception handling.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
> You are probably on the right track with your speculation. Connections
> and commands take longer when there's a network or SQL problem. Without a
> governor mechanism in place, client requests can accumulate to the point
> of resource starvation and an application restart or server reboot is
> often the easiest and fastest way to get things running again.
> The effect of network/SQL issues can be mitigated by setting a limit on
> concurrent application requests. This assumes applications are coded in
> such a way as to be resilient following a problem. For example, a
> middle-tier app with a persistent database connections will need to be
> smart enough to gracefully retry database connections instead of requiring
> a restart. Exception handling needs to be especially thorough and tested
> accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
>|||> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
Here's an article that discusses database mirroring failover retry but the
same retry pattern can be used in general:
http://www.microsoft.com/technet/pr...er.mspx

There are many articles on exception handling best practices. Here are a
couple recent ones for .Net.
http://www.ftponline.com/vsm/2007_0...rp/default.aspx
http://www.codeproject.com/dotnet/e...stpractices.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uUA94ZJzHHA.748@.TK2MSFTNGP04.phx.gbl...
> This is what I was looking for to hear and the more I can obtain knowledge
> on this area, the better.
> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
>|||On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Every once in a while our SQL Servers hiccup or take a blip in the form of
>blocking,etc.. and when this happens our Web and Application servers begin
>to misbehave and our system engineers begin to reboot these servers to
>resolve the issue.
If SQL Server connections are being blocked, as shown by EXEC sp_who,
rebooting is an extreme response. The first thing to check for is a
long running task that is holding locks. If there is one connection
doing the blocking then KILL is quicker and more selective than a
reboot. Of course it is best to figure out what that connection is
doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
Roy Harvey
Beacon Falls, CT|||Roy,
I meant we reboot our web and application servers and not out SQL Servers.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:hr89a3drmd47bfsvqhn0kkhf13ar92dvto@.
4ax.com...
> On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> If SQL Server connections are being blocked, as shown by EXEC sp_who,
> rebooting is an extreme response. The first thing to check for is a
> long running task that is holding locks. If there is one connection
> doing the blocking then KILL is quicker and more selective than a
> reboot. Of course it is best to figure out what that connection is
> doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
> Roy Harvey
> Beacon Falls, CT|||On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Roy,
>I meant we reboot our web and application servers and not out SQL Servers.
So that drops all the connections from the client side. It still
seems like it would be worth looking at KILLing a specific connection
rather than taking the application off line long enough for a reboot.
Of course it requires much more knowledge.
Roy Harvey
Beacon Falls, CT|||Our web and app servers go bonkers and thats what I am trying to figure..
why they do so when there is a small blip on the SQL server ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:iop9a3hb9ca2ql3a6hgh3dasp20p33duqf@.
4ax.com...
> On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> So that drops all the connections from the client side. It still
> seems like it would be worth looking at KILLing a specific connection
> rather than taking the application off line long enough for a reboot.
> Of course it requires much more knowledge.
> Roy Harvey
> Beacon Falls, CT

May not be a SQL question but you may have the answer..

Every once in a while our SQL Servers hiccup or take a blip in the form of
blocking,etc.. and when this happens our Web and Application servers begin
to misbehave and our system engineers begin to reboot these servers to
resolve the issue. They do not have a solid answer, but I think these
servers try to open up more connections and probably consume all threads and
memory( not too sure, just speculating)..
Have you seen this kind of behavior and if so, why is it per your
environment and what have you done to avoid those reboots by making the Web
and app servers smart to know what to do when there are SQL failures? Its
one thing to have SQL conk off which is costly, but then we spend another
good portion of our time rebooting every other non SQL Server too. Sounds
like some bad way of programming but I want to take your inputs to my
programmers/architects.
Thank you>From your question, i assume that you will never restart SQL Server
services, and only webserver and app's, and if thats the case i have a
strong feeling that SQL Server is not causing the issue. I had seen in
very rare scenerios where SQL will use all the 255 (Default) worker
threads, and if its with memory, by restarting other services, SQL
will not release its memory. Since you are speculating, this info is
just for your understanding.
Also you might want to collect more symptoms as to what is happening
on SQL when you are seeing the issue, like CPU spike, from
sysprocesses check and see if any extensive blocking (though these
might not be related, but getting all symptoms will always give you
answers )|||Dinu. thanks for the response.
I do know whats happening to SQL, but the problem exacerbates to where the
web and app servers suffer and need to be restarted and want to know how can
we make the app servers smart
<dinu_babu@.hotmail.com> wrote in message
news:1185077813.592904.45890@.e9g2000prf.googlegroups.com...
> >From your question, i assume that you will never restart SQL Server
> services, and only webserver and app's, and if thats the case i have a
> strong feeling that SQL Server is not causing the issue. I had seen in
> very rare scenerios where SQL will use all the 255 (Default) worker
> threads, and if its with memory, by restarting other services, SQL
> will not release its memory. Since you are speculating, this info is
> just for your understanding.
> Also you might want to collect more symptoms as to what is happening
> on SQL when you are seeing the issue, like CPU spike, from
> sysprocesses check and see if any extensive blocking (though these
> might not be related, but getting all symptoms will always give you
> answers )
>|||You are probably on the right track with your speculation. Connections and
commands take longer when there's a network or SQL problem. Without a
governor mechanism in place, client requests can accumulate to the point of
resource starvation and an application restart or server reboot is often the
easiest and fastest way to get things running again.
The effect of network/SQL issues can be mitigated by setting a limit on
concurrent application requests. This assumes applications are coded in
such a way as to be resilient following a problem. For example, a
middle-tier app with a persistent database connections will need to be smart
enough to gracefully retry database connections instead of requiring a
restart. Exception handling needs to be especially thorough and tested
accordingly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Every once in a while our SQL Servers hiccup or take a blip in the form of
> blocking,etc.. and when this happens our Web and Application servers begin
> to misbehave and our system engineers begin to reboot these servers to
> resolve the issue. They do not have a solid answer, but I think these
> servers try to open up more connections and probably consume all threads
> and memory( not too sure, just speculating)..
> Have you seen this kind of behavior and if so, why is it per your
> environment and what have you done to avoid those reboots by making the
> Web and app servers smart to know what to do when there are SQL failures?
> Its one thing to have SQL conk off which is costly, but then we spend
> another good portion of our time rebooting every other non SQL Server too.
> Sounds like some bad way of programming but I want to take your inputs to
> my programmers/architects.
> Thank you
>|||This is what I was looking for to hear and the more I can obtain knowledge
on this area, the better.
Is there any whitepaper or anything on this subject that I can forward to
the devs. Basically they need to know how to make the apps more resilient
and have the right exception handling.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
> You are probably on the right track with your speculation. Connections
> and commands take longer when there's a network or SQL problem. Without a
> governor mechanism in place, client requests can accumulate to the point
> of resource starvation and an application restart or server reboot is
> often the easiest and fastest way to get things running again.
> The effect of network/SQL issues can be mitigated by setting a limit on
> concurrent application requests. This assumes applications are coded in
> such a way as to be resilient following a problem. For example, a
> middle-tier app with a persistent database connections will need to be
> smart enough to gracefully retry database connections instead of requiring
> a restart. Exception handling needs to be especially thorough and tested
> accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Every once in a while our SQL Servers hiccup or take a blip in the form
>> of blocking,etc.. and when this happens our Web and Application servers
>> begin to misbehave and our system engineers begin to reboot these servers
>> to resolve the issue. They do not have a solid answer, but I think these
>> servers try to open up more connections and probably consume all threads
>> and memory( not too sure, just speculating)..
>> Have you seen this kind of behavior and if so, why is it per your
>> environment and what have you done to avoid those reboots by making the
>> Web and app servers smart to know what to do when there are SQL failures?
>> Its one thing to have SQL conk off which is costly, but then we spend
>> another good portion of our time rebooting every other non SQL Server
>> too. Sounds like some bad way of programming but I want to take your
>> inputs to my programmers/architects.
>> Thank you
>|||> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
Here's an article that discusses database mirroring failover retry but the
same retry pattern can be used in general:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
There are many articles on exception handling best practices. Here are a
couple recent ones for .Net.
http://www.ftponline.com/vsm/2007_06/magazine/columns/csharp/default.aspx
http://www.codeproject.com/dotnet/exceptionbestpractices.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uUA94ZJzHHA.748@.TK2MSFTNGP04.phx.gbl...
> This is what I was looking for to hear and the more I can obtain knowledge
> on this area, the better.
> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
>> You are probably on the right track with your speculation. Connections
>> and commands take longer when there's a network or SQL problem. Without
>> a governor mechanism in place, client requests can accumulate to the
>> point of resource starvation and an application restart or server reboot
>> is often the easiest and fastest way to get things running again.
>> The effect of network/SQL issues can be mitigated by setting a limit on
>> concurrent application requests. This assumes applications are coded in
>> such a way as to be resilient following a problem. For example, a
>> middle-tier app with a persistent database connections will need to be
>> smart enough to gracefully retry database connections instead of
>> requiring a restart. Exception handling needs to be especially thorough
>> and tested accordingly.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Every once in a while our SQL Servers hiccup or take a blip in the form
>> of blocking,etc.. and when this happens our Web and Application servers
>> begin to misbehave and our system engineers begin to reboot these
>> servers to resolve the issue. They do not have a solid answer, but I
>> think these servers try to open up more connections and probably consume
>> all threads and memory( not too sure, just speculating)..
>> Have you seen this kind of behavior and if so, why is it per your
>> environment and what have you done to avoid those reboots by making the
>> Web and app servers smart to know what to do when there are SQL
>> failures? Its one thing to have SQL conk off which is costly, but then
>> we spend another good portion of our time rebooting every other non SQL
>> Server too. Sounds like some bad way of programming but I want to take
>> your inputs to my programmers/architects.
>> Thank you
>>
>|||On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>Every once in a while our SQL Servers hiccup or take a blip in the form of
>blocking,etc.. and when this happens our Web and Application servers begin
>to misbehave and our system engineers begin to reboot these servers to
>resolve the issue.
If SQL Server connections are being blocked, as shown by EXEC sp_who,
rebooting is an extreme response. The first thing to check for is a
long running task that is holding locks. If there is one connection
doing the blocking then KILL is quicker and more selective than a
reboot. Of course it is best to figure out what that connection is
doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
Roy Harvey
Beacon Falls, CT|||Roy,
I meant we reboot our web and application servers and not out SQL Servers.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:hr89a3drmd47bfsvqhn0kkhf13ar92dvto@.4ax.com...
> On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>>Every once in a while our SQL Servers hiccup or take a blip in the form of
>>blocking,etc.. and when this happens our Web and Application servers begin
>>to misbehave and our system engineers begin to reboot these servers to
>>resolve the issue.
> If SQL Server connections are being blocked, as shown by EXEC sp_who,
> rebooting is an extreme response. The first thing to check for is a
> long running task that is holding locks. If there is one connection
> doing the blocking then KILL is quicker and more selective than a
> reboot. Of course it is best to figure out what that connection is
> doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
> Roy Harvey
> Beacon Falls, CT|||On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>Roy,
>I meant we reboot our web and application servers and not out SQL Servers.
So that drops all the connections from the client side. It still
seems like it would be worth looking at KILLing a specific connection
rather than taking the application off line long enough for a reboot.
Of course it requires much more knowledge.
Roy Harvey
Beacon Falls, CT|||Our web and app servers go bonkers and thats what I am trying to figure..
why they do so when there is a small blip on the SQL server ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:iop9a3hb9ca2ql3a6hgh3dasp20p33duqf@.4ax.com...
> On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>>Roy,
>>I meant we reboot our web and application servers and not out SQL Servers.
> So that drops all the connections from the client side. It still
> seems like it would be worth looking at KILLing a specific connection
> rather than taking the application off line long enough for a reboot.
> Of course it requires much more knowledge.
> Roy Harvey
> Beacon Falls, CT

maximum of DBprocess already allocated

Hi all,
Sql server 7.0
our client is getting the below error while accessing the web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIATHis should help . . . (watch wraparound)

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q164/1/71.asp&NoWebContent=1&NoWebContent=1|||hi thanks for the reply

the error we are getting is given more detailed below

DBPool - Failed to initialize RWDBConnection
Unknown Exception caught in DBConnPool::createConnection() -- RWDatabase::connection() or validation of the connection returned failed

Database middleware messages:
ErrorCode: vendorLib Msg VENDORLIB] Vendor Library Error: Maximum number of DBPROCESSes already allocated

Pls help!!!!

Maximum number of Conversation Timers?

Is there an upper limit on the number of Conversation Timers?

We're using Conversation Timers to implement a retry mechanism for outbound web service calls. The idea is to use Service Broker to queue up a number of web service requests. If a web service call fails to contact it's target server, we would like to reschedule that request to retry after a few minutes delay. It appears that when we have a few thousand Conversation Timers queued up, SQL Server (June CTP) will crash, or at least cause a failover to it's mirror partner.

TIA -- Keith.

There is no hard limit on the number of conversations. However, conversations timers do require system resources (memory, processor time, tempdb space, etc) and if you push more than your hardware can handle, the system might become unresponsive and this can cause mirroring to failover.

In order to decide whether this is the case or something else is happening, I would need more info.
- Is it really a crash or just a failover? Does the SQL LOG folder contain any dumps?
- What kind of hardware is this happening on: architecture, number and speed of procs, RAM available?
- When the failover occurs, any entry appear in the ERRLROG or in the system EventViewer, on the pricnipal or on the mirror?

Thanks,
~ Remus

|||

We are running an load test which is attempting to benchmark the maximum capability of the system. The h/w configuration:

2 x HP DL380's running the SQL 2005 June CTP on Windows Server 2003 Standard Edition SP1. The servers are using synchronous Database Mirroring with a 3rd machine acting as a Witness. Each DL380 has 2 x 3.4GHz Xeon's and 4GB RAM. Each DL380 also has a RAID5 SCSI array attached.

@.@.VERSION = Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

There are also 2 x HP DL360's running Windows Server 2003 Web Edition SP1. Each DL360 has 2 x 3.4GHz Xeon's with lots of RAM. These are being used as load-balanced web application front ends, running ASP.NET 2.0. There are also 3 .NET 2.0 based windows services running against the database.

There are no indications of a crash: no dumps in the SQL LOG folder. The starting PRIMARY server has the following log entries at the time of failover:

09/09/2005 21:25:36,Logon,Unknown,Login failed for user 'XXXUser'. [CLIENT: 10.0.10.14]
09/09/2005 21:25:36,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
09/09/2005 21:25:36,spid27s,Unknown,Bypassing recovery for database 'XXX' because it is marked as a mirror database<c/> which cannot be recovered. This is an informational message only. No user action is required.
09/09/2005 21:25:35,spid27s,Unknown,Starting up database 'XXX'.
09/09/2005 21:25:34,Logon,Unknown,Login failed for user 'XXXUser'. [CLIENT: 10.0.10.15]
09/09/2005 21:25:34,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
09/09/2005 21:25:34,spid17s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 21:20:17,Backup,Unknown,Log was backed up. Database: XXX<c/> creation date(time): 2005/09/02(23:31:06)<c/> first LSN: 2489:314:1<c/> last LSN: 2492:2360:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\SQL_Data\MSSQL.1\MSSQL\Backup\tlogs\XXX_backup_200509092120.TRN'}). This is an informational message only. No user action is required.

The error messages for failed logons continue to repeat. The starting SECONDARY server has the following log entries at the time of failover:

09/09/2005 21:49:37,spid15s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 21:25:39,spid15s,Unknown,Database mirroring is active with database 'XXX' as the principal copy. This is an informational message only. No user action is required.
09/09/2005 21:25:35,spid15s,Unknown,Recovery is writing a checkpoint in database 'XXX' (5). This is an informational message only. No user action is required.
09/09/2005 21:25:33,spid15s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 15:51:17,spid15s,Unknown,Database mirroring is active with database 'XXX' as the mirror copy. This is an informational message only. No user action is required.

This is an order processing application using 4 service broker queues all in the same database. One queue is being used by the two ASP.NET 2.0 front-ends for Order Entry. The three .NET 2.0 services each process the remaining three queues. Each of the services is multi-threaded with 4, 16, and 16 threads respectively. Each thread will wait on it's respective queue for an available request.

The system overall runs as expected when no conversation timers are injected into the queues. The application front-ends run at nearly 100% CPU utilization (as expected) and the database servers seems to run at around 20% CPU without extensive memory pressure.

Simulating one of our failure conditions causes the program logic to use the conversation timers in one of the queues as part of the retry mechanism. The failover and logon problems start after only a few minutes of running our load test. I would estimate that less than 500 orders have been processed at that time.

-Keith.

|||How big are the conversation timer intervals?

If I understand correctly, you say that when there are about 500 conversation timers 'armed' for a timeout of X, the system will become unresponsive (user logins are denied, mirroring timeouts occur triggering failover etc).

we'd better take this offline, I will probably have more questions for you and some of the details probably you don't want them posted on a public forum. Can you send me at r e m u s r @. m i c r o s o f t . c o m a mail address where I can contac yout?

I will post back the conclusion here.

Thanks,
~ Remus