How does SQL Anywhere decide when to grow the SYSTEM dbspace, and by how much?

[Breck_Carter]

The following result set shows a 12.0.1.3298 database that is growing fairly steadily in terms of the number of rows and file size, until it reaches a certain point when suddenly the file size is almost tripled, and the free space grows from essentially zero to about 55%... this is surprising behaviour, not upsetting, but startling:

Time,          Rows,File_MB,Free_MB
'10:40:01',       0,   14,   0
'11:00:02',     198,   14,   0
'11:20:01',     558,   14,   0
'11:40:34',  113284,   87,   1
'12:00:41',  265519,  184,   1
'12:20:39',  417402,  281,   1
'12:40:07',  566554,  465,  87
'13:00:35',  722714,  478,   3
'13:20:08',  875287,  578,   4    - fairly steady growth to here
'13:40:19', 1023136, 1473, 805    - then a sudden huge leap upwards in file size and free space
'14:00:07', 1176820, 1474, 707
'14:20:09', 1332550, 1474, 609
'14:40:36', 1488646, 1474, 510
'15:00:14', 1640930, 1474, 413

Does SQL Anywhere get exasperated with repeated requests for more space and just say "here, take a whole bunch"?

---

PS: Those measurements are snapshots taken every 20 minutes, they give no indication whether the Great Leap Forward was all at once or in multiple steps.

[johnsmirnios · Accepted solution]

When the file grows on demand, the current algorithm is to grow the file by 1.5625% of the current size of the database but at least 32K and no more than 32MB. By those numbers, only files smaller than 2MB or larger than 2GB would have growth clamped by the 32K or 32MB limits.

[Breck_Carter · Accepted solution]

Do you have any idea why the file size and especially free space would climb suddenly ...the application is doing nothing but steadily inserting new rows.

[johnsmirnios · Accepted solution]

Was exactly the same thing running for the whole period? The figures would be consistent with doing a large number of inserts followed by a rollback (or deletes/truncate). I don't think there's anything in the growth algorithm itself that can describe the behaviour you are seeing. The "cleaner" task might account for pages suddenly becoming available but I don't think it can account for the growth.

You could take a look at a translated transaction log to see what went on during those 20 minutes. Remember to include rolled back transactions when translating the log.

[Breck_Carter · Accepted solution]

Yes, it was doing exactly the same thing for the whole period... inserting rows at the same rate, no rollbacks, no deletes. So, it is a mystery 🙂

[johnsmirnios · Accepted solution]

Just to be explicit, is that conclusion based on looking at the transaction log or just what you knew was going on? My concern is that an event could have done something that might grow the file.

[Breck_Carter · Accepted solution]

The conclusion is based on what I know is going on: The Foxhound monitor was inserting rows at a steady state. There is no event or other process that would suddenly insert and then delete a lot of data. The only process that deletes data is the purge, and it keeps meticulous records of how many rows it deletes... it is the purge, in fact, that provided the numbers shown here, and it deleted zero rows.

So... let's just drop it. The database and log are long gone, and I'm not gonna reproduce it just to have the pleasure of translating 1.5G of transaction log data. Most problems are caused by "user error" so it's not going to hurt to throw this mystery onto that heap 🙂