Help diagnosing issues with IIS / DB (out of memory / timeout)

  • We have a large web based system ASP/SQL 2000/DTSearch Indexing that has run successfully for over a year now but in the last 4 months we have started getting certain issues which we cannot get to the bottom of.

     

    I would have thought our System Admin guys would have had tools to diagnose and determine what these issues are and what’s causing them but apparently they haven't got any or don't know how to so I am trying to get some help to see if anyone else could help us determine what issues are occurring.

     

    We have a web server with 40 websites (including the one having problems), Quad P3 700mhz, 4gb RAM

     

    The DB has about 60 databases on it (the largest two are experiencing connection timeout issues), Dual P4 2.8 GHz, 4gb RAM

     

    IIS6 is running on the web server, DTSearch is running 6.5

     

    My site is set up with custom error handler pages that email me with any ASP errors that occur on the site.

     

    I am concerned with my site although other sites using this set up experience similar problems so I don't know if they are related. There are two separate issues that I do not know whether are connected so a way to diagnose each would be helpful.

     

    Out of Memory Problems.

     

    These occur on any/all pages in the site. Pages with hardly any ASP code > lots of ASP code. A typical error alert looks like this.

     

    ASPCode =

    ASPDescription =

    Category = Microsoft VBScript compilation

    Column = -1

    Description = Out of memory

    File = /jobboard/cands/jobview.asp

    Line = 0

    Number = -2146827287

    Source =

    Last SQL = EXEC dbo.usp_asp_get_category_list @CategoryList='7313,7322,7323';

     

     

    I always store the last sql statement I run in a session var especially for these email alerts. As I said the page could be one of 150, the sql statement could be any of hundreds. It always says line 0. I have looked in the IIS logs and have never found anything helpful at all. I have looked on the web for related issues and can't see anything relevant eg (older versions of IIS don't handle long sql strings very well, or looping through lots of nested arrays etc) we defragged the hard drive as well. I have searched my code and don't have any recursive functions that would go into infinity. I always destroy my DB connections, objects at end of page/class calls. Although I thought IIS 6 was good at garbage collecting. We also use DTSearch to build up text indexes for user searching. Sometimes when trying to generate one of these indexes DTSearch reports back that it could not get enough memory allocated to the resource so this process bottoms out. Usually if we re-start ISS when these memory issues occur then it resolves them all until the next time.

     

    Timeout problems

     

    Another intermittent problem that can come in batches of 100-500 suddenly and then stop is timeouts whilst connecting to the DB. An example of an error alert is 

     

    ASPCode =

    ASPDescription =

    Category = Microsoft VBScript runtime

    Column = -1

    Description = Object required: 'objDataCon.ExecSQL(...)'

    File = /jobboard/scripts/classes/banner.class.asp

    Line = 299

    Number = -2146827864

    Source =

    Last SQL = EXEC dbo.usp_asp_get_banner_advert @search = '', @SitePk = 12, @GroupPK = 2; = -2147217871 SQL Error: [Microsoft][ODBC SQL Server Driver]Timeout expired

    Form info =

     

    This sql statement is usually the first sql run on all pages (in the header.asp.inc file for all my sites), which tries to get a banner to display. If you try to visit a site during these error blitzes the page just hangs as it tries to load. Someone suggested deadlocks might be causing this.

     

    Both of these issues have started occurring 3-4 months ago. The site could run for a week with no problems and then suddenly have a rush of either (timeouts/memory issues). They don't coincide with each other or with any big jobs that are run on the servers or anything out of the ordinary.

     

    The guys are thinking of doing the following (in order to try and resolve issues)

    -Upgrade version of DTSearch

    -Servers are currently in-house, outsource them

    -Convert code for indexing to use SQL2005 rather than DTSearch 

     

    I would like to know what the actual problem are rather than just change everything in the hope of resolving it.

     

    Are there not Profile Traces that could be run in the hope of catching info about the issues, or some sort of monitoring code for ISS/SQL? Has anyone got any helpful advice I can pass on to the people who are doing this? If it’s a code issue then it needs fixing, if it’s a set-up issue it also needs looking at. 

     

    Any help would be much appreciated. Thanks.

     

  • This was removed by the editor as SPAM

  • Update.

     

    I have created a new error_logging DB and every time my custom 500.100.asp reports an out of memory or DB timeout I log it.

    I also log every time an instance of the DTSearch COM object is instantiated as I think it might be a memory leak from this object.

    This way I can run reports to see if heavy use of the object coincides with the problems.

     

    I was wondering what other information would be wise to log as more the better.

     

    Is there a way I can log any of the following at the time of logging either in the 500.100.asp or the log stored proc.

     

    -Memory use at time (DB / Web)

    -Free Disk space at time (DB / Web)

    -No of connections in use

    -Any deadlocks and the sql causing them

    -Any SQL of long running processes at the time.

     

    Anything else that could be useful.

     

    Also does anyone know of any good tools that could be run on the server that would help hunt a memory leak?

     

    I have also read somewhere that it could be memory fragmentation if I'm getting Out of Memory errors but it says there are lots

    of free memory.

     

    Any help at all would be much appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply