Search:   Namespaces Discussions .NET v1.1 Feedback

ASPX pages failing when SQL Server busy Messages   Related Types This message was discovered on microsoft.public.dotnet.framework.aspnet. Post a new message to this list... 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 Reply to this message...        http://www.visual-basic-data-mining.net/forum 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" <Click here to reveal e-mail address> wrote in message news:%Click here to reveal e-mail address... > I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET [Original message clipped] Reply to this message...        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 "http://www.visual-basic-data-mining.net/forum" <Click here to reveal e-mail address> wrote in message news:%233L%Click here to reveal e-mail address... [Original message clipped] Reply to this message...        John 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 "John" <Click here to reveal e-mail address> wrote in message news:%Click here to reveal e-mail address... [Original message clipped] Reply to this message...        Miha Markic [MVP C#] 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 "John" <Click here to reveal e-mail address> wrote in message news:%Click here to reveal e-mail address... [Original message clipped] Reply to this message...        Mark 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" <Click here to reveal e-mail address> wrote in message news:%Click here to reveal e-mail address... > I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET [Original message clipped] Reply to this message...        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 "Mark" <Click here to reveal e-mail address> wrote in message news:<Click here to reveal e-mail address>... [Original message clipped] Reply to this message...     System.Data.SqlClient.SqlException Ad MBR BootFX Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd. Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com