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.

asked 27 Nov '11, 15:20

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 28 Nov '11, 08:11


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.

permanent link

answered 28 Nov '11, 10:12

John%20Smirnios's gravatar image

John Smirnios
11.9k396165
accept rate: 37%

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.

(28 Nov '11, 10:16) Breck Carter
Replies hidden

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.

(28 Nov '11, 10:27) John Smirnios

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 :)

(28 Nov '11, 14:58) Breck Carter

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.

(28 Nov '11, 20:53) John Smirnios
Comment Text Removed

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 :)

(29 Nov '11, 06:10) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×275

question asked: 27 Nov '11, 15:20

question was seen: 1,807 times

last updated: 29 Nov '11, 06:10