long running queries quit - audit logout

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Gary (gld_at_hotmail.com)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 17:01:59 GMT

I have a web based application that launches sql queries using ADO from
ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
result sets are sent off to Crystal Reports at the end of the run with the
report being displayed in a browser window. In general this all works well
is has been very reliable.

Lately I've been having trouble with some of these queries on databases that
have gotten quite large (25 - 40G) but only a couple of the tables have
rowcounts in the tens of millions, most are relatively small. The behavior
is that the first query runs, sometimes for up to 90 minutes, seems to
complete, and then the pid is gone. Looking at the profile trace, I can see
that the query executed nearly 13 M reads with a duration of over 90
minutes. The line following the SQL:BatchCompleted event is Audit Logout,
and thats the last trace of the process. Under "normal" operation, the next
query would start and they would all run until the loop is complete. I've
seen this now with 2 completely different sets of queries, on 2 different
dbs, yet the symptoms are the same. It completes a very simple query that
returns a single datetime value. Then the first "real" query runs, anywhere
from 60 - 90 minutes or more, and then the process goes away.

It seems as if I've reached some threshold or internal timeout. I have the
connection time and query timeouts set to very large values (1000 minutes),
so it's not that.The client process is of course IIS. This is running SQL
Server 2000 SP3, NT 4.0, IIS 5.5.

I have had sets of queries that have taken as long as 3 hours to complete,
but each individual query probably never exceeded 45 minutes in duration.

Can anyone think of what is causing this to happen -- why does the pid go
away after the first query completes? Is there some other internal timeout
somewhere that I'm running into?

Any advice is appreciated,

-Gary

Relevant Pages

  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)