Alec Swan wrote:
Our app rebuilds indexes periodically (about once a month) by calling "ALTER INDEX index_name ACTIVE" on a bunch of indexes in the database. I need to estimate how much free disk space the customer should have in order to complete this process. I decided to restrict TempDirectories size, run the command and see when it starts failing.
Here are my findings:
1. TempDirectories = C:\Temp\Firebird Running "ALTER INDEX idx_physical_copy_commit_number ACTIVE" succeeds 2. TempDirectories = C:\Temp\Firebird 100000000000 Running "ALTER INDEX idx_physical_copy_commit_number ACTIVE" causes "GDS Exception. 335544675. sort error"
The database size is 1.5GB so 100 GB should be more than enough to activate any index, right?
My main question is how can we calculate the temp sort space required to ALTER INDEX ACTIVE (on all indexes serially) given the size of the database? Is twice the size of the database a good upper bound? We will provide this as a recommendation to our customers, so it has to be simple.
Vlad Khorsun answers:
Enumerate all fields in index, calculate summa of full size of every field (for [var]char(N) it is N * bytes_per_char, for ex.), add extra 8 bytes (for record number) - this is sort record size.
Multiply it by number of records and you'll get a lower estimate of temp space required. Substract value of TempCacheLimit and you'll get estimate of disk space required.
Alec Swan continues:
Thanks Vlad, but this is too complicated to give as a guidance to our customers. However, it sounds like rebuilding an index cannot require more space that the database size itself, right?
Ann W. Harrison answers:
On a bad day, yes it could. To have a day that bad, you'd have to have keys that are most of the size of the record, with columns that are often null, zero, or contain values much smaller than the declared size of the column. There may be a situation during the merge phase of the sort when there's more than one copy of each record.