Is there a way to control how or when database grows?

[former_SQLA_member1694893]

I'm used to increasing the database size (using ALTER DBSPACE) monthly or weekly after verifying that the free pages reaches a threshold (for instance, 5% of the total pages). Then, I run a desfragmentation tool in the OS as recommended here. Regarding the increasing procedure, is there any way to specify how the database handle this situation (in order to let them do this automatically)? Or ff I didn't do this, How would SQLA behave? How many pages would it allocate and when?

[MCMartin · Accepted solution]

You can create an Event of type GrowDB, it will be called whenever the database server needs to grow any of its files. We use inside the function the free pages count (select db_property('FreePages')) to determine if really the db file is the reason for the event. If so, an "alter dbspace SYSTEM add" is used with the desired increasing step to pre allocate the db file e.g. with 1 Giga.

[VolkerBarth · Accepted solution]

...alternatively you can use a scheduled event that runs daily/nightly and does all those checks automatically that you currently do manually by coding an according handler.

In contrast to the "GrowDb"/"GrowLog" events, it could increase the file size before the file has grown automatically, which might be more performant.

[former_SQLA_member1694893 · Accepted solution]

@Volker: I think this approach is better because, as you said, you could act before it needs to grow. Thanks. @Martin: Thanks, I forgot that I could use events!

[justin_willey]

This is what the v12.0.1 docs say here:

If space is not preallocated, database files are extended by about 256 KB at a time for page sizes of 2 KB, 4 KB, and 8 KB, and by about 32 pages for other page sizes, when the space is needed. Pre-allocating space can improve performance for loading large amounts of data and also serves to keep the database files more contiguous within the file system.

It doesn't say what exactly "when the space is needed" means!

[VolkerBarth]

The following FAQ tells the details of the "growth algorithm"...