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?

asked 11 Jan '12, 14:18

MarcosCunhaLima's gravatar image

MarcosCunhaLima
3067918
accept rate: 0%

edited 12 Jan '12, 06:43

Justin%20Willey's gravatar image

Justin Willey
6.6k106136205


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.

permanent link

answered 12 Jan '12, 06:46

Martin's gravatar image

Martin
8.6k115149237
accept rate: 14%

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:

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!

permanent link

answered 12 Jan '12, 05:55

Justin%20Willey's gravatar image

Justin Willey
6.6k106136205
accept rate: 21%

edited 12 Jan '12, 06:02

1

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

(12 Jan '12, 07:09) Volker Barth
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:

×17
×7
×2

question asked: 11 Jan '12, 14:18

question was seen: 867 times

last updated: 12 Jan '12, 13:42