|
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? |
|
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. 1
...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.
(12 Jan '12, 06:52)
Volker Barth
@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!
(12 Jan '12, 13:42)
MarcosCunhaLima
|
|
This is what the v12.0.1 docs say here:
It doesn't say what exactly "when the space is needed" means! |