Professional Web Applications Themes

ADO client disconnects after running a long query - ASP Components

I am having a problem executing long running queries from an ASP application which connects to SQL Server 2000. Basically, I have batches of queries that are run using ADO in a loop written in VBScript. This works pretty well until the execution time of a single query starts to exceed some threshold, which I am trying to narrow down. I can typically run 2 - 10 queries in a loop, with the run time being anywhere from under a minute to an hour or more. Now that this application is being subjected to run against some large databases (25 ...

  1. #1

    Default ADO client disconnects after running a long query

    I am having a problem executing long running queries from an ASP application
    which connects to SQL Server 2000. Basically, I have batches of queries that
    are run using ADO in a loop written in VBScript. This works pretty well
    until the execution time of a single query starts to exceed some threshold,
    which I am trying to narrow down. I can typically run 2 - 10 queries in a
    loop, with the run time being anywhere from under a minute to an hour or
    more. Now that this application is being subjected to run against some large
    databases (25 - 40G), I'm having problems getting the application to
    continue beyond the first query if it takes a while to run.

    I used SQL Profiler to try to diagnose what was going on. I can see the
    query executes to completion, but immediately after completing I can see an
    "Audit Logout" message, which apparently means that the client has
    disconnected. The query durations vary from 45 or 50 minutes to up to over
    90 minutes. I have the ADO connection and query timeouts set to very large
    values, e.g. 1000 minutes, so I can't think its that. My guess is that there
    is some IIS setting or timeout that I am running up against and the
    connection to SQL Server is just dropped for some reason.

    The configuration is

    NT 4.0 SP6
    SQL Server 2000 SP3
    IIS 4.0
    Internet Explorer 5.5

    I'm only running into this problem on the very largest databases we run
    against. The vast majority continue to function properly, but this is going
    to happen more often as time goes on the databases continue to grow in size.

    Any advice is appreciated,

    -Gary




    Gary Guest

  2. #2

    Default Re: ADO client disconnects after running a long query

    Gary wrote:
    > I am having a problem executing long running queries from an ASP
    > application which connects to SQL Server 2000. Basically, I have
    > batches of queries that are run using ADO in a loop written in
    > VBScript. This works pretty well until the execution time of a single
    > query starts to exceed some threshold, which I am trying to narrow
    > down. I can typically run 2 - 10 queries in a loop, with the run time
    > being anywhere from under a minute to an hour or more. Now that this
    > application is being subjected to run against some large databases
    > (25 - 40G), I'm having problems getting the application to continue
    > beyond the first query if it takes a while to run.
    >
    > I used SQL Profiler to try to diagnose what was going on. I can see
    > the query executes to completion, but immediately after completing I
    > can see an "Audit Logout" message, which apparently means that the
    > client has disconnected. The query durations vary from 45 or 50
    > minutes to up to over 90 minutes.
    !??!?
    Don't use ASP for this. Why tie up a thread on your web server for that
    long??

    I'm willing to be that this entire task could be done within a scheduled
    stored procedure.

    > I have the ADO connection and query
    > timeouts set to very large values, e.g. 1000 minutes, so I can't
    > think its that. My guess is that there is some IIS setting or timeout
    Probably ScriptTimeout


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: ADO client disconnects after running a long query

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:udDZnd$fEHA.1972TK2MSFTNGP09.phx.gbl...
    > Don't use ASP for this. Why tie up a thread on your web server for that
    > long??
    >
    I have no choice at the moment. I realize it's not an optimal solution. Also
    there are rarely more than a couple requests at a time going on.
    > I'm willing to be that this entire task could be done within a scheduled
    > stored procedure.
    Some of it is scheduled, but it's designed to work on-demand as well.
    >
    > > I have the ADO connection and query
    > > timeouts set to very large values, e.g. 1000 minutes, so I can't
    > > think its that. My guess is that there is some IIS setting or timeout
    >
    > Probably ScriptTimeout
    This is already set to a really large value. (way more than an hour) But
    thanks for the suggestions.

    -Gary







    Gary Guest

  4. #4

    Default Re: ADO client disconnects after running a long query

    Gary wrote:
    > "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:udDZnd$fEHA.1972TK2MSFTNGP09.phx.gbl...
    >> Don't use ASP for this. Why tie up a thread on your web server for
    >> that long??
    >>
    >
    > I have no choice at the moment. I realize it's not an optimal
    > solution. Also there are rarely more than a couple requests at a time
    > going on.
    >
    >> I'm willing to be that this entire task could be done within a
    >> scheduled stored procedure.
    >
    > Some of it is scheduled, but it's designed to work on-demand as well.
    You can kick off a scheduled job that runs a stored procedure using
    sp_start_job. To pass parameter values to the procedure, you can insert them
    into a control table where they can be read by the stored procedure.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

Similar Threads

  1. Unexpected Client Disconnects
    By DanielKozimor in forum Macromedia Flash Flashcom
    Replies: 4
    Last Post: August 17th, 02:08 AM
  2. Replies: 0
    Last Post: August 11th, 06:18 PM
  3. Finish execution of ASP script when client disconnects
    By Boris Nikolaevich in forum ASP
    Replies: 4
    Last Post: September 4th, 05:17 AM
  4. microsoft client disconnects on reboot
    By kent in forum Windows Networking
    Replies: 1
    Last Post: July 15th, 04:55 PM
  5. Killing long running queries
    By Meenal Dhody in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139