Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Anabik
Starting Member
9 Posts |
 Posted - 2011-12-11 : 09:41:04
|
Hi,We recently shifted our website to a new server at a new hosting place. After the shifting the website hangs a lot & we need to recycle the application pool. After recycling it works fine. The servers got Windows 2003 in it and the website is on ASP.NET 1.1 with SQL 2000.We have checked the code & there are no loop holes in the code. Tried searching on google for the same, but got no solution. The server has only 1 website on it. So creating a separate application pool for it wont solve the problem.The website the works fine when the problem occurs, its only the pages which get data from SQL get hanged up. We upgraded the SQL 2000 to service pack 4 but still its not helping.There have been a few errors too in the event viewer regarding w3svc & http.We thought the bandwidth from our hosting people was causing the problem so we upgraded the bandwidth, but still the same issue occurs.After posting the same on IIS forums someone told me to post the issue here since it might be the SQL 2000 thats causing the problem.Is it really the SQL 2000 thats causing the website to get hanged after some intervals? Any kind of help or suggestion is appreciated.Thanks.  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-11 : 11:16:27
|
| very difficult to determine what's the exact problem with information given. Do you've any entries in your error log related to sql. Also is this happening across website or only for specific pages? If latter, are those pages showing large amount of data? are they using inline queries or stored procedures?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-11 : 17:02:44
|
| I am presuming that there is basically no change in the application / users / "load" - just you moved to the New Hosting Company?Could be to do with extension of the database / log files - in SQL 2000 that caused considerable delay, and blocking whilst it happened. If the database is of considerable size, and auto-expansion is set to (say) 10% [rather than fixed size of MB] that could be the problem.Solution: change to fixed size of MB which is more modest and/or extend the files "significantly" to prevent auto-expansion.Might also be auto-update of statistics or somesuch.These things caused blocking back in SQL 2000, and on busy web sites users would start pressing RETRY - which caused half-built pages to be discarded and rebuilt from scratch, doubling (or tripling ...) the work load for the servers.if you just did a BACKUP from your old site, and RESTORE on the new one, then you should have the same Database Properties as before (unless you/hosting company fiddled with them), but if you transferred by some other means ??? (e.g. scripted and created tables, then migrated data) then the Hosting company's default settings may be the issue.if its not that then my best guess would be some sort of side-effect from virtualisation.Move to SQL 2008 if you can - problem with auto-file-extension is now solved, and you can set Auto Statistics update to be asynchronous, plus a host of other new "features" that will give you more scope to solve the problem. You'll need a full regression test to prove that latest version of SQL has not side-effects / does not introduce any new bugs though. |
|
|
|
Anabik
Starting Member
9 Posts |
Posted - 2011-12-11 : 23:33:22
|
Hi Visakh..It doesnt happen across the website...only the pages that have some operation related to SQL...The website worked fine before..its only when we shifted the new hosting place the problem has started and we are not able to figure out whats the issue...I have also noticed that when the website gets stuck instead of refreshing the application pool...if only stop & start the SQL services the website starts to work again....quote:
Originally posted by visakh16
very difficult to determine what's the exact problem with information given. Do you've any entries in your error log related to sql. Also is this happening across website or only for specific pages? If latter, are those pages showing large amount of data? are they using inline queries or stored procedures?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
Anabik
Starting Member
9 Posts |
Posted - 2011-12-11 : 23:36:56
|
Hi KristenI believe the hosting guys didnt fiddled with SQL...I will try that auto-expansion setting.Thanks quote:
Originally posted by Kristen
I am presuming that there is basically no change in the application / users / "load" - just you moved to the New Hosting Company?Could be to do with extension of the database / log files - in SQL 2000 that caused considerable delay, and blocking whilst it happened. If the database is of considerable size, and auto-expansion is set to (say) 10% [rather than fixed size of MB] that could be the problem.Solution: change to fixed size of MB which is more modest and/or extend the files "significantly" to prevent auto-expansion.Might also be auto-update of statistics or somesuch.These things caused blocking back in SQL 2000, and on busy web sites users would start pressing RETRY - which caused half-built pages to be discarded and rebuilt from scratch, doubling (or tripling ...) the work load for the servers.if you just did a BACKUP from your old site, and RESTORE on the new one, then you should have the same Database Properties as before (unless you/hosting company fiddled with them), but if you transferred by some other means ??? (e.g. scripted and created tables, then migrated data) then the Hosting company's default settings may be the issue.if its not that then my best guess would be some sort of side-effect from virtualisation.Move to SQL 2008 if you can - problem with auto-file-extension is now solved, and you can set Auto Statistics update to be asynchronous, plus a host of other new "features" that will give you more scope to solve the problem. You'll need a full regression test to prove that latest version of SQL has not side-effects / does not introduce any new bugs though.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-12 : 00:05:09
|
quote:
Originally posted by Anabik
Hi Visakh..It doesnt happen across the website...only the pages that have some operation related to SQL...The website worked fine before..its only when we shifted the new hosting place the problem has started and we are not able to figure out whats the issue...I have also noticed that when the website gets stuck instead of refreshing the application pool...if only stop & start the SQL services the website starts to work again....quote:
Originally posted by visakh16
very difficult to determine what's the exact problem with information given. Do you've any entries in your error log related to sql. Also is this happening across website or only for specific pages? If latter, are those pages showing large amount of data? are they using inline queries or stored procedures?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Can you try running a profiler trace if possible when you access these problem sites and see whats happening on the background?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Anabik
Starting Member
9 Posts |
Posted - 2011-12-12 : 02:21:51
|
quote:
Originally posted by visakh16
quote:
Originally posted by Anabik
Hi Visakh..It doesnt happen across the website...only the pages that have some operation related to SQL...The website worked fine before..its only when we shifted the new hosting place the problem has started and we are not able to figure out whats the issue...I have also noticed that when the website gets stuck instead of refreshing the application pool...if only stop & start the SQL services the website starts to work again....quote:
Originally posted by visakh16
very difficult to determine what's the exact problem with information given. Do you've any entries in your error log related to sql. Also is this happening across website or only for specific pages? If latter, are those pages showing large amount of data? are they using inline queries or stored procedures?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Can you try running a profiler trace if possible when you access these problem sites and see whats happening on the background?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I have gone thru Kristen's suggestion....checked the auto-expansion thing on the old server & found out the values are set to default and the values are set to default in the new server too..What i have been doing for past few days is checking values on the old server with the new one...but havent been able to find out the problem since almost all the settings are on their default values and the website worked perfectly on the old server...The values are in default settings in the new server also, but the website is getting stuck every now & then & needs application pool recycling....I am not able to find out whats causing it and its driving me insane.. |
|
|
|
|
|
|
|
|
Website gets stuck...