We have a number of databases that have started hanging and not allowing any new connections or new SQL to be ran on existing connections, sometimes for a few minutes and sometimes for an hour or so. Usually they just spring back into life and continue working.

SQL Anywhere version (There's a bug somewhere in the later versions of 10 that caused us some other issues so have been on 3960 for a long time now)

We've tried

  • Using request logging to see if it's a piece of SQL that is causing it but there's nothing obvious in there and if we rerun them after the server doesn't hang.
  • Application Profiling which doesn't show anything we could see as useful for this
  • DBUnload the database into a new one
  • These server settings -gc 1 -gr 1 -gn 25
  • Breck's max query tasks (last/first resort) option

Does anyone have an suggestions for diagnosing the issue or even settings we can try to work around it ?

Thanks Daz.

asked 27 Feb, 10:58

Daz%20Liquid's gravatar image

Daz Liquid
accept rate: 28%


Does that appear when a checkpoint is done? IMHO during a checkpoint the database may appear "hanging", although the large timespan you describe would be strange for a checkpoint. Nevertheless, I guess the forum had some questions on checkpoints and unresponsive essay...

(27 Feb, 14:36) Volker Barth

Try removing the -gc 1 -gr 1... unless you are absolutely certain you know better than SQL Anywhere 10 how often a checkpoint should occur (hint: if so, you'll be the first :)

Then, try Foxhound.

(27 Feb, 17:00) Breck Carter
Replies hidden

We just added those to see if they would work :-) More than happy to try foxhound, was looking at it again today to see if it could help but we weren’t sure if it could with us not being able to find anything in the request logs. Do you think it can ?

(27 Feb, 17:13) Daz Liquid

AFAIK a checkpoint is not recorded in the RLL, as it is not a request... But during a checkpoint there's a phase where all current requests are suspended, which might be the phenomenon you are observing...

(28 Feb, 02:24) Volker Barth

I'll have a look and see what happens next time it hangs and if the database starts a checkpoint beforehand, assuming it does what could cause a checkpoint to take such a long time ?

(28 Feb, 04:52) Daz Liquid

Can it be that the all database pages are full and the database engine is extending the dbspaces ?

(28 Feb, 06:16) Thomas Dueme...

There is no guarantee that Foxhound will show you where the problem is... there is a very real possibility that Foxhound itself will be shut out during the hour-long period of unresponsiveness.

However, something might show up before the unresponsiveness starts.

Request logs are sometimes useful for debugging, but almost never for performance tuning.

What are the cache settings?

(28 Feb, 07:19) Breck Carter

> the phenomenon you are observing

...which is an hour-long period of unresponsiveness?

(28 Feb, 07:20) Breck Carter

You certainly are using a transaction log for these databases, right?

(28 Feb, 09:48) Volker Barth
Replies hidden

Sure am, checked dbconsole at the time of the hang too and it hadn't started a checkpoint (or at least hadn't added the checkpoint started msg).

(28 Feb, 12:01) Daz Liquid

Two "server unresponsive" bugs have been fixed since

    ================(Build #4128  - Engineering Case #642524)================

The server could have become unresponsive when processing index scans in 
which a residual predicate continually rejected candidate rows for the duration 
of the scan.  This has been fixed.

================(Build #4089  - Engineering Case #623779)================

Servers running databases with large schemas may experience periods of unresponsiveness 
at idle checkpoint time. The performance of checkpoints has been improved 
to reduce the length of this interval.
(01 Mar, 07:49) Breck Carter

It can be that long yes, sometimes only 10mins.

(01 Mar, 08:16) Daz Liquid

Installed foxhound and you're right it gets #1 when the database becomes unresponsive. I can't see anything obvious beforehand in foxhound, any tips for what we should be looking at ?

Cache settings -c 43P -ch 43P

(01 Mar, 08:26) Daz Liquid

You might have a look at the console.log file itself. If the database becomes unresponsive, dbconsole is usually affected, as well, whereas the file output to the console log should still happen, and the start and end of a checkpoint are always logged there, so that should tell you if a checkpoint is the cause of the delay...

(01 Mar, 09:12) Volker Barth

Switched that on too and will have a look, thanks.

(02 Mar, 10:45) Daz Liquid
showing 5 of 15 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 27 Feb, 10:58

question was seen: 203 times

last updated: 02 Mar, 10:45