|
|
You're in the
sql server programming
group:ASPX pages failing when SQL Server busy
sql server programming:
Modify the web.config file to allow for asp.net errors to when accessed from the local web server, but not the clients. Then log into the local web server and try our your application. Something like: <customErrors mode="RemoteOnly" /> or similar. Too many factors involved below to NOT find out what the exact message is. Also - make the tempdb a static size at 3 GB. Don't let it autogrow. That will kill performance in a hurry. Also, your connection string should include a time out setting that is higher than normal. Good luck. Mark www.dovetaildatabases.com [quoted text, click to view] "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... > I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET > webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > Everything works fine, but sometimes when I execute a particular stored > procedure (on server console using command line, nothing .net), my webapps > will fail intermitently. Custom errors are turned on, so at this stage I > can't give you error codes! But my assumption is that it's a SQL problem, > not a .NET one. > > The sproc does lots of joining, aggrigates and sorting, and makes the tempdb > grow from nothing to over 3 gigs. The database it's actually in is around > 300mb in size, and doesn't grow that much itself. All db's are in 'simple' > recovery model. The sproc contains no explicit transactions or temp tables. > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because of > it's massive growth. Could it be getting too big for the filesystem??? (I > have lots of disc space free, but maybe W2k can't cope with a single file > larger than x?) > > Thanks, > > John > >
I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. Everything works fine, but sometimes when I execute a particular stored procedure (on server console using command line, nothing .net), my webapps will fail intermitently. Custom errors are turned on, so at this stage I can't give you error codes! But my assumption is that it's a SQL problem, not a .NET one. The sproc does lots of joining, aggrigates and sorting, and makes the tempdb grow from nothing to over 3 gigs. The database it's actually in is around 300mb in size, and doesn't grow that much itself. All db's are in 'simple' recovery model. The sproc contains no explicit transactions or temp tables. Can anyone suggest what's going wrong?? I suspect tempdb, if only because of it's massive growth. Could it be getting too big for the filesystem??? (I have lots of disc space free, but maybe W2k can't cope with a single file larger than x?) Thanks, John
John,
You might start by catching the errors and logging them to see where the problem is. -- Miha Markic [MVP C#] - RightHand .NET consulting & development miha at rthand com www.rthand.com [quoted text, click to view] "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... >I have a single server running SQL Server 2000 sp3a, and acting as a >ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > Everything works fine, but sometimes when I execute a particular stored > procedure (on server console using command line, nothing .net), my webapps > will fail intermitently. Custom errors are turned on, so at this stage I > can't give you error codes! But my assumption is that it's a SQL problem, > not a .NET one. > > The sproc does lots of joining, aggrigates and sorting, and makes the > tempdb grow from nothing to over 3 gigs. The database it's actually in is > around 300mb in size, and doesn't grow that much itself. All db's are in > 'simple' recovery model. The sproc contains no explicit transactions or > temp tables. > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because > of it's massive growth. Could it be getting too big for the filesystem??? > (I have lots of disc space free, but maybe W2k can't cope with a single > file larger than x?) > > Thanks, > > John >
The error from the aspx pages is:
"Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The timeout is the default 30 seconds. This occured on multiple apps, using unrelated databases (but all the same server, and same instance). They all seemed to exhibit timeout behaviour, they all stopped responing for 30 seconds +, even though some of the apps were using different db's so nothing could be locked (and the big sproc that's running isn't using any explicit transactions). The problems seem to appear just before the sproc finished (after about 20 minutes of running). Help!!!! John [quoted text, click to view] "Mark" <field027@idonotlikejunkmail.umn.edu> wrote in message news:<ujouAzelEHA.2864@TK2MSFTNGP14.phx.gbl>...
> Modify the web.config file to allow for asp.net errors to when accessed from > the local web server, but not the clients. Then log into the local web > server and try our your application. Something like: > > <customErrors mode="RemoteOnly" /> or similar. > > Too many factors involved below to NOT find out what the exact message is. > > Also - make the tempdb a static size at 3 GB. Don't let it autogrow. That > will kill performance in a hurry. Also, your connection string should > include a time out setting that is higher than normal. > > Good luck. > > Mark > www.dovetaildatabases.com > > > "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message > news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... > > I have a single server running SQL Server 2000 sp3a, and acting as a > ASP.NET > > webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > > > Everything works fine, but sometimes when I execute a particular stored > > procedure (on server console using command line, nothing .net), my webapps > > will fail intermitently. Custom errors are turned on, so at this stage I > > can't give you error codes! But my assumption is that it's a SQL problem, > > not a .NET one. > > > > The sproc does lots of joining, aggrigates and sorting, and makes the > tempdb > > grow from nothing to over 3 gigs. The database it's actually in is around > > 300mb in size, and doesn't grow that much itself. All db's are in 'simple' > > recovery model. The sproc contains no explicit transactions or temp > tables. > > > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because > of > > it's massive growth. Could it be getting too big for the filesystem??? (I > > have lots of disc space free, but maybe W2k can't cope with a single file > > larger than x?) > > > > Thanks, > > > > John > >
The stored procedure may be poorly designed. Analyze the execution plan of
the stored procedure to look for joins and statements that you can further optimize -- data mining and .net team http://www.visual-basic-data-mining.net/forum [quoted text, click to view] "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... > I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET > webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > Everything works fine, but sometimes when I execute a particular stored > procedure (on server console using command line, nothing .net), my webapps > will fail intermitently. Custom errors are turned on, so at this stage I > can't give you error codes! But my assumption is that it's a SQL problem, > not a .NET one. > > The sproc does lots of joining, aggrigates and sorting, and makes the tempdb > grow from nothing to over 3 gigs. The database it's actually in is around > 300mb in size, and doesn't grow that much itself. All db's are in 'simple' > recovery model. The sproc contains no explicit transactions or temp tables. > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because of > it's massive growth. Could it be getting too big for the filesystem??? (I > have lots of disc space free, but maybe W2k can't cope with a single file > larger than x?) > > Thanks, > > John > >
The error i'm getting is a timeout:
"Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The timeout is the default 30 seconds. This occured on multiple apps, using unrelated databases (but all the same server, and same instance). They all seemed to exhibit timeout behaviour, they all stopped responing for 30 seconds +, even though some of the apps were using different db's so nothing could be locked (and the big sproc that's running isn't using any explicit transactions). The problems seem to appear just before the sproc finished (after about 20 minutes of running). How can I fix it!?????!! John [quoted text, click to view] "http://www.visual-basic-data-mining.net/forum" <simiking@gmail.com> wrote in message news:%233L%234rjlEHA.3988@tk2msftngp13.phx.gbl... > The stored procedure may be poorly designed. Analyze the execution plan of > the stored procedure to look for joins and statements that you can further > optimize > > -- > data mining and .net team > http://www.visual-basic-data-mining.net/forum > > > "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message > news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... >> I have a single server running SQL Server 2000 sp3a, and acting as a > ASP.NET >> webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. >> >> Everything works fine, but sometimes when I execute a particular stored >> procedure (on server console using command line, nothing .net), my >> webapps >> will fail intermitently. Custom errors are turned on, so at this stage I >> can't give you error codes! But my assumption is that it's a SQL problem, >> not a .NET one. >> >> The sproc does lots of joining, aggrigates and sorting, and makes the > tempdb >> grow from nothing to over 3 gigs. The database it's actually in is around >> 300mb in size, and doesn't grow that much itself. All db's are in >> 'simple' >> recovery model. The sproc contains no explicit transactions or temp > tables.
Is it possible that, following a massive update on one production database
(several hundred thousand rows being deleted / inserted), that locks in tempdb could escalate to table locks?? Would that cause the problems I'm seeing? (ie the whole instance of SQL Server basically going single-task for about 2 minutes) If so, how can I avoid this escalation in tempdb? Thanks, John [quoted text, click to view] "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message news:%23ebBl2rlEHA.3608@TK2MSFTNGP09.phx.gbl... > The error i'm getting is a timeout: > > "Error: System.Data.SqlClient.SqlException: Timeout expired. The > timeout period elapsed prior to completion of the operation or the > server is not responding." > > The timeout is the default 30 seconds. This occured on multiple apps, > using unrelated databases (but all the > same server, and same instance). They all seemed to exhibit timeout > behaviour, they all stopped responing for 30 seconds +, even though some > of the apps were using different db's so nothing could be locked > (and the big sproc that's running isn't using any explicit transactions). > The problems seem to appear just before the sproc > finished (after about 20 minutes of running). > > How can I fix it!?????!! > > John > > "http://www.visual-basic-data-mining.net/forum" <simiking@gmail.com> wrote > in message news:%233L%234rjlEHA.3988@tk2msftngp13.phx.gbl... >> The stored procedure may be poorly designed. Analyze the execution plan >> of >> the stored procedure to look for joins and statements that you can >> further >> optimize >> >> -- >> data mining and .net team >> http://www.visual-basic-data-mining.net/forum >> >> >> "John" <jsparrowNOSPAM@ecclesdeletethiscollege.ac.uk> wrote in message >> news:%23YZJ0belEHA.3496@TK2MSFTNGP12.phx.gbl... >>> I have a single server running SQL Server 2000 sp3a, and acting as a >> ASP.NET >>> webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. >>> >>> Everything works fine, but sometimes when I execute a particular stored >>> procedure (on server console using command line, nothing .net), my >>> webapps >>> will fail intermitently. Custom errors are turned on, so at this stage I >>> can't give you error codes! But my assumption is that it's a SQL >>> problem, >>> not a .NET one. >>> >>> The sproc does lots of joining, aggrigates and sorting, and makes the >> tempdb >>> grow from nothing to over 3 gigs. The database it's actually in is >>> around >>> 300mb in size, and doesn't grow that much itself. All db's are in >>> 'simple' >>> recovery model. The sproc contains no explicit transactions or temp >> tables. > >
First thing, I would't run the web server on the same machine as SQLServer.
It will kill the consistency of your site's performance. [quoted text, click to view] "John" wrote:
> I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET > webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > Everything works fine, but sometimes when I execute a particular stored > procedure (on server console using command line, nothing .net), my webapps > will fail intermitently. Custom errors are turned on, so at this stage I > can't give you error codes! But my assumption is that it's a SQL problem, > not a .NET one. > > The sproc does lots of joining, aggrigates and sorting, and makes the tempdb > grow from nothing to over 3 gigs. The database it's actually in is around > 300mb in size, and doesn't grow that much itself. All db's are in 'simple' > recovery model. The sproc contains no explicit transactions or temp tables. > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because of > it's massive growth. Could it be getting too big for the filesystem??? (I > have lots of disc space free, but maybe W2k can't cope with a single file > larger than x?) > > Thanks, > > John > >
Sure, TempDb is used quite a bit as a "scratchpad" to hold temporary (intermediate) products and to sort the data. What kind of hard drives do you have? Is the database segmented so it's not putting TempDb on the same spindle as the OS and the other databases. Sure, you might be disk bound. If you're sharing space with IIS, that could also hurt performance as could the complexity of your SP. It could be that the query plan does not match the set of parameters you're using. It could be that your connection pool might be filling up as inbound customers can't get a connection because the ones that are in use are still busy. It could be a dozen dozen other things as well. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ [quoted text, click to view] "Luke" <Luke@discussions.microsoft.com> wrote in message news:7E5D4A67-DC60-4DC3-B2EF-01435E559D0C@microsoft.com... > First thing, I would't run the web server on the same machine as > SQLServer. > It will kill the consistency of your site's performance. > > "John" wrote: > >> I have a single server running SQL Server 2000 sp3a, and acting as a >> ASP.NET >> webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. >> >> Everything works fine, but sometimes when I execute a particular stored >> procedure (on server console using command line, nothing .net), my >> webapps >> will fail intermitently. Custom errors are turned on, so at this stage I >> can't give you error codes! But my assumption is that it's a SQL problem, >> not a .NET one. >> >> The sproc does lots of joining, aggrigates and sorting, and makes the >> tempdb >> grow from nothing to over 3 gigs. The database it's actually in is around >> 300mb in size, and doesn't grow that much itself. All db's are in >> 'simple' >> recovery model. The sproc contains no explicit transactions or temp >> tables. >> >> Can anyone suggest what's going wrong?? I suspect tempdb, if only because >> of >> it's massive growth. Could it be getting too big for the filesystem??? (I >> have lots of disc space free, but maybe W2k can't cope with a single file >> larger than x?) >> >> Thanks, >> >> John >> >> >>
Sounds like your sql is bad, and possibly forgot a join, causing a cartesian
product. Break apart the code and test, perhaps with SQL Profiler Jeff [quoted text, click to view] "Luke" <Luke@discussions.microsoft.com> wrote in message news:7E5D4A67-DC60-4DC3-B2EF-01435E559D0C@microsoft.com... > First thing, I would't run the web server on the same machine as SQLServer. > It will kill the consistency of your site's performance. > > "John" wrote: > > > I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET > > webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. > > > > Everything works fine, but sometimes when I execute a particular stored > > procedure (on server console using command line, nothing .net), my webapps > > will fail intermitently. Custom errors are turned on, so at this stage I > > can't give you error codes! But my assumption is that it's a SQL problem, > > not a .NET one. > > > > The sproc does lots of joining, aggrigates and sorting, and makes the tempdb > > grow from nothing to over 3 gigs. The database it's actually in is around > > 300mb in size, and doesn't grow that much itself. All db's are in 'simple' > > recovery model. The sproc contains no explicit transactions or temp tables. > > > > Can anyone suggest what's going wrong?? I suspect tempdb, if only because of > > it's massive growth. Could it be getting too big for the filesystem??? (I > > have lots of disc space free, but maybe W2k can't cope with a single file > > larger than x?) > > > > Thanks, > > > > John > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |