Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLLCould it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL|||We are on SQL Server 2000, no error message that we have exceeded Work Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
"Warren Brunk" wrote:
> Could it be that you were over your max worker threads?
> http://msdn2.microsoft.com/en-us/library/ms187024.aspx
> When all worker threads are active with long running queries, SQL Server may
> appear unresponsive until a worker thread completes and becomes available.
> Though not a defect, this can sometimes be undesirable. If a process appears
> to be unresponsive and no new queries can be processed, then connect to SQL
> Server using the dedicated administrator connection (DAC), and kill the
> process. To prevent this, increase the number of max worker threads.
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> > Recieved this error this morning, at the same time are users were kicked
> > out
> > of SQL Server.
> >
> > We have anout 1500 connections at a time. There are no SQL Error log
> > entries
> > other than the Transaction Log failing, no Application Events or System
> > Events.
> >
> > The server did not go down, the CPUs (8) went to almost zero.
> >
> > SQL Server Enterprise Manager was getting a network error message when
> > trying to connect and within 5 minutes everything was OK.
> >
> > Any help?
> >
> > Thanks,
> > ThomasLL
>
>|||> Are you using any third party backup software?
No
We are on SQL Server 2000, no error message that we have exceeded Work
Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
--
Thanks,
ThomasLL,
"Sue Hoegemeier" wrote:
> Are you using any third party backup software?
> You can also get this error if you are running low on system
> resources - mostly memory.
> You may have too much of a server load at the time you tried
> to do the backup. You'd want to get some baselines numbers
> using Performance Monitor.
> -Sue
> On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
> >Recieved this error this morning, at the same time are users were kicked out
> >of SQL Server.
> >
> >We have anout 1500 connections at a time. There are no SQL Error log entries
> >other than the Transaction Log failing, no Application Events or System
> >Events.
> >
> >The server did not go down, the CPUs (8) went to almost zero.
> >
> >SQL Server Enterprise Manager was getting a network error message when
> >trying to connect and within 5 minutes everything was OK.
> >
> >Any help?
> >
> >Thanks,
> >ThomasLL
>|||You are correct about the worker threads which is why I
didn't suggest changing that setting. On SQL 2000, you will
almost always have problems if you increase the value from
the default 255. It is not advisable and you would want to
be monitoring UMS stats as well as other indicators before
even considering changing that.
But...most of the time the error you received is really due
to resource constraints. A one time snapshot of a few
performance metrics isn't going to tell you much. The
article on sql-server-performance.com for performance audits
provides some good information on what you want to check:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
-Sue
On Mon, 9 Oct 2006 13:32:01 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>> Are you using any third party backup software?
>No
>We are on SQL Server 2000, no error message that we have exceeded Work
>Threads.
>We do have 8 processors and 64 Gigs, neither are maxed out.
>Thanks for your responce.
>Most sites I have read say you should NOT increase worker threads because of
>overhead with more threads.
>Cache Hit Ratio 101.05824
>Cache Flushes 0.0
>Free Page Scan (Avg) 0.0
>Free Page Scan (Max) 0.0
>Min Free Buffers 331.0
>Cache Size 4362.0
>Free Buffers 2261.0
>--
>Thanks,
>ThomasLL|||Thanks everyone!!!
--
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
No comments:
Post a Comment