Problem with ALTER DATABASE CALIBRATE DBSPACE TEMPORARY;

[justin_willey]

From time to time we find it worthwhile running CALIBRATE statements on servers, especially those with fancy disk systems - sometimes the improvement in performance can be quite dramatic.

Usually we just run

ALTER DATABASE CALIBRATE SERVER;

which works nicely and according to the docs calibrates all dbspaces except the TEMPORARY dbspace. However in a recent case, we thought we might as well do the TEMPORARY dbspace as well - but

ALTER DATABASE CALIBRATE DBSPACE TEMPORARY;

produces this error:

For what it's worth

ALTER DATABASE CALIBRATE DBSPACE SYSTEM;

works fine. I've tried it on a variety of systems from v10 to v16 - all with the same result.

Am I doing something idiotic?

[Breck_Carter · Accepted solution]

Try growing the temporary file first, as with this query on a large table:

   SELECT a.* INTO #t FROM t AS a, t AS b;

[justin_willey · Accepted solution]

That'll be it then - ooops.

[justin_willey · Accepted solution]

As Breck originally said - but then tactfully edited out, if I'd looked up SQLCode -860 properly, I'd have seen that the issue is that the TEMPORARY DBSPACE can't be calibrated until it has reached an unspecified minimum size.

As you have to have exclusive access to the database to run a calibration, I was doing it after a restart of the database, at which point the temporary dbspace would have been tiny. So as Breck points out, the solution is to do something to grow it using your own connection, before trying to calibrate it.

Moral of the story (as so often) - RTFM.

[huber1 · Accepted solution]

Im my environment on a Mac, the

ALTER DATABASE CALIBRATE DBSPACE temporary

returns SQLQODE=-308 AND shuts down my database server (dbsrv17).

I executed previously the mentioned SELECT stmt for a table to grow the temporary file, but it did not help (in my case). The statement I executed is: select tc.* into #timetable_classes from timetable_classes as tc, timetable_classes as tc1.

Can I see the temporary table #timetable_classes somewhere?

I noticed that in SQL Central menu Dbspaces > temporary is still empty after executing the above SELECT. Is this to be expected?

I also wonder what typical performance gain one can expect executing the various ALTER DATABASE CALIBRATE statements? Or does this extremely vary so no useful statement can be made?

Regards, Robert

DB in use: SQL Anywhere 17.0.10.5853