long running queries quit - audit logout

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,
-GaryYou might be experiencing lock_timeout. See if it helps:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
"Gary" <gld@.hotmail.com> wrote in message
news:bisSc.243431$IQ4.52808@.attbi_s02...
> 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
>
>|||Thanks for the tip. I checked a server and it's returning -1 (infinite) for
this. I'm thinking that the problem is the client connection to the database
dropping sometime during the processing of a query, and when the statement
complete event occurs, SQL detects that the connection is gone and therefore
the audit logoff event.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7lkZQ$fEHA.3632@.TK2MSFTNGP09.phx.gbl...
> You might be experiencing lock_timeout. See if it helps:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
>

Logout takes 20 minutes?

I have a problem where our middle tier and all clients connected to it stops
responding for 5 to 20 minutes once or twice a day.
I now did a Profiler trace and saw that there is an Audit Logout entry with
the following data:
EventClass:Audit Logout
CPU:3641
Reads:203866
Writes:0
Duration:1272060
Can anybody tell my why this sometimes take so long, and how to stop it?
Thanx,
ErrolThe logout event duration is the amount of time the user was logged in and
is not directly related to your problem.
Check for rpc completed and batch completed events for exceptionally long
durations. The symptoms you describe may be related to blocking so check
for blocked processing using sp_who2.
Hope this helps.
Dan Guzman
SQL Server MVP
"Errol Terblanche" <Errol Terblanche@.discussions.microsoft.com> wrote in
message news:54DCBF8F-DBFC-41D5-9062-78D64DA01799@.microsoft.com...
>I have a problem where our middle tier and all clients connected to it
>stops
> responding for 5 to 20 minutes once or twice a day.
> I now did a Profiler trace and saw that there is an Audit Logout entry
> with
> the following data:
> EventClass:Audit Logout
> CPU:3641
> Reads:203866
> Writes:0
> Duration:1272060
> Can anybody tell my why this sometimes take so long, and how to stop it?
> Thanx,
> Errol
>|||Also check for any processes that are in rollback state. You might have
uncommitted transactions that are rolling back.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Ou9V1enwEHA.1192@.tk2msftngp13.phx.gbl...
> The logout event duration is the amount of time the user was logged in and
> is not directly related to your problem.
> Check for rpc completed and batch completed events for exceptionally long
> durations. The symptoms you describe may be related to blocking so check
> for blocked processing using sp_who2.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Errol Terblanche" <Errol Terblanche@.discussions.microsoft.com> wrote in
> message news:54DCBF8F-DBFC-41D5-9062-78D64DA01799@.microsoft.com...
>|||I have looked for blocks in the Process Info window, but there where none.
I will get a more complete trace to see if i can see anything else.
There are no Batches that took more that 170ms.
I'm starting to think it might be ADO as the middle tier sets up the
connection but it hangs at the connection.connect statement. When this
happens all users that connect gets a connection assigned to them but they
all hang at the connection.connect statement. The connection is set up to
timeout after 15 seconds, but this doesn't happen so i don't think it is
truely a connection problem. After some time all the pending connections
suddenly open, practically all at once.
I have also set up a second middle tier connecting to the same database and
it runs fine and connects promptly even when the first middle tier is
hanging...
Any advice?
Thanx,
Errol
"Mike Epprecht (SQL MVP)" wrote:

> Also check for any processes that are in rollback state. You might have
> uncommitted transactions that are rolling back.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Ou9V1enwEHA.1192@.tk2msftngp13.phx.gbl...
>
>