Web page not responding

[Guest]

Hi,

I have an issue on windows 2003 thats proving really hard to track down.

The site works fine most of the day but at various times, once the web page
has been submitted, the SQL database gets updated but the web page never
returns, I eventually get a timeout. Stop and restart SQL server 2000 SP3
and all is fine again. That is until several hours later when the problem
starts happening again.

This issue only ever seems to effect database updates, any read only access
is fine.

Any help would be appreciated.

Pete.

 

[Guest]

Pete,

Have you tried looking at SQL server's current activity at the time of the
timeout to see exactly what is going on in the database? It sounds to me
like a record locking problem during the update. I would suggest looking at
this information (you can use Query Analyzer and sp_who2 to see the current
processes) to see if the table is being accessed by another query either
reading or if multiple processes are trying to update that table at the same
time. From that information you can then look into treating the record
locking problem (if that is what is wrong) by enabling dirty reads from the
readers or specifying a table hint on the updating query (e.g. UPDLOCK). You
can find more information about record locking/blocking problems in SQL
Server at http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453

I hope that this helps!

 

[Guest]

Hi Brian,

Thanks for the reply, although I dont think it is locking, as it can happen
with only one active user on the system. Plus the behaviour is apparent with
any update to any table. ie. by selecting different updates on different
pages each effecting different tables. SQL current activity -->processes
did't show any blocking.

Although I will try and ascertain what SQL is doing at the time of the
timeout. My initial thought was a a .NET connection leak (ie. haddent been
disposed of properly)but looking under current processes I can only see that
there has been 2 processes started for the impersonated user we use for the
SQL access from .NET.

Regards, Pete.

 

[WJ]

Hi Brian,

Thanks for the reply, although I dont think it is locking, as it can
happen
with only one active user on the system. Plus the behaviour is apparent
with
any update to any table. ie. by selecting different updates on different
pages each effecting different tables. SQL current activity -->processes
did't show any blocking.

Do you close connection after each request ?

John Webb

 

[Guest]

Hi Pete,

I have a few questions...
In your first post you said that you restart SQL. Do you also reset IIS?

During the timeout of the web page have you tried to query SQL Server from a
different web page running on a different IIS (such as someones local
machine) or open Query Analyzer and try to query for data? Do you get
anything back?

Have you determined if the connections were disposed of properly?

Sorry for the flurry of questions but I am trying to narrow down where the
problem is occuring. SQL or IIS (or something else). If you have already
figured it out please post so we can all benefit.

Good Luck!

 

[Guest]

Hi Brian,

No, I dont have to restart IIS just SQL. While the site exibits this
behaviour, I did try to update some data via SQL Enterprise manager. For a
simple row update, on a small table there was a delay of about 30-40 seconds
where it looked like enterprise manager wasn't responding. Then the update
went through. Then try another update via enterprise manager and it goes
through instantly.

Next step is to ensure that my data access layer closes and disposes of all
SQL connections as this seems to be as if one or more connections may be
preventing other connections from updating.

Pete.

 

[Guest]

John,

Thats exactly where I was going with this issue. My next step is to trawl
through the data access layer ensure all connections are closed and disposed.

I will post back when I complete this task. May take some time.

Pete.

 

[Guest]

Brian,

Just tried it again and noticed something else.

Web page keeps timing out on the update, when this behaviour apparent, I
performed an update via SQL Enterprise managed. Looked like SQL enterprise
manager hung for about 1 min. then update went through. Further update went
through immediately. Go back to the web page and perform the same update that
hung originally and perform the update again. Update goes through fine. So
whatever SQL enterprise manager was doing, it managed to sort the problem out.

Well for a while at least.

Pete.

 

[Guest]

Pete,

Hmmm….. Enterprise Manager locking up…. It really sounds like you are
having a problem with your Sql Server not ASP or IIS. It is possible that
the problem could be caused due to a lack of disposing connections. I think
that we should keep that on the table but I do think that we should look at
other parameters too.

Can you tell me the specs of your Sql Server (e.g. RAID setup, processors
[type, ghz], memory [GB], file groups [locations on disk])? What kind of
UPDATE are you doing batch, by cursor, single row?

I think that we are near the problem. I look forward to your reply!

Good luck.

 

[Guest]

Hi Brian,

I think I may have found it. It looks like someone has been messing with the
SQL server settings. I couldn't link this behaviour to a particular event.
ie. after specific updates had been performed - it didn't have any
consistency at all. Therefore I decided to look through all SQL server
settings and noticed a setting that had been enabled that I havent seen
enabled before.

Auto Close was set in the database options. This now starts to make a little
more sense. Apparently when this option is set, as soon as all connections
are closed and all processes complete, then SQL server stops the databases
?????? apparently useful for desktop servers so the data files can be treated
as normal files ie. copied, moved etc.

So if SQL databases shut down - first access will restart them, time delay -
SQL timeout from .Net Sql client. Update through enterprise manager - takes a
while but restarts the database. Then all access from IIS etc OK as there is
no delay.

It certainly looks like this is the issue, it certainly fits with the
symptoms, but I will have to wait for a while to see if the behaviour
reoccurs.

After revisiting the data access layer of our intranet, I was supprised how
well I coded the DAL. Each method within the DAL that performs data access
always closes and disposes of the connections, also the DAL implements
IDISPOSABLE interface and the dispose method closes any open connections and
disposes of them. All instantiations of the DAL from the object layer have
accompanying DAL.Dispose() when finished with the data.

Well heres hoping that we have located the issue. I will post back and let
you know. Thanks for all your input during the previous posts - you certainly
made me think more in terms of a SQL issue other than IIS.

Thanks. Pete.

 

[Guest]

Hi Pete,

Great job! Auto close will do just as you say (close down the db after the
last user logs out) and it does fit right into the symptoms of the problem.
I am sure that disabling this will alleviate your problems.

Good Luck!

 

[Guest]

Hi Brian,

We got it! Over 24hrs now without symptoms re appearing. Whereas before, it
would have definately had happened within a 24hour period - no to find the
culprit who as been messing with SQL settings - I'll shoot them

Once again thanks for your input and time spent.

Pete.

 

[Guest]

Pete,

That is great to hear! I am glad that I could help!