I had previously run truncate on the same database on the backup server and got a much greater reduction in the file size.
The backup server is relatively inactive, while the main server had database activity during the reorg process. Could this have a bearing on why not all the free blocks are recovered? If not, what else could have an effect? What can I do to recover all of the available free blocks?
George
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
While offline reorg (and defragmentation) is virtually guaranteed to give an optimally packaged database, online reorg cannot because while it is busy compacting blocks and moving them towards the front of the database file, other processes are busy allocating blocks - possibly newly freed ones - and using them. After a reorg, truncate then starts at the rear of the databases, and works towards the front of the database chopping pieces off and returning the space to the operating system. So, assuming that your database had free space available that truncate did not free, it means that as it worked from the end of the database towards the beginning, truncate found a local bit map and related blocks that were not 100% free. This can have two causes:
1. An active processes needed space and chose that local bit map to allocate space. Although processes have a bias for finding space near the front of a database file, there is a certain amount of randomness because there are multiple processes accessing the database and processes do not guarantee to use the available free space nearest to the front of the database file. If this is the case, and there is plenty of free space available, running the online reorg again, perhaps with less application load, would free more space.
2. The root block of a global variable is in one of the blocks of that last local bitmap. The performance related heuristics of the database engine assume that the root block of a global variable, once allocated, is never freed. If this is the case, then you would have to use DSE to perform surgery on the database (possible, but not recommended) - or extract the data and load it into a newly created database.
Regards
- Bhaskar
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi
I've been enjoying the new -truncate feature of 5.5, managing to recover lots of free space from my bloated databases.
However, I experienced an unexpected result with one database and I wonder if anyone can explain what might have happened here.
The database is 220Gb with a block size of 64Kb.
I ran the following:
Why did it not truncate the file any further?
I had previously run truncate on the same database on the backup server and got a much greater reduction in the file size.
The backup server is relatively inactive, while the main server had database activity during the reorg process. Could this have a bearing on why not all the free blocks are recovered? If not, what else could have an effect? What can I do to recover all of the available free blocks?
George
While offline reorg (and defragmentation) is virtually guaranteed to give an optimally packaged database, online reorg cannot because while it is busy compacting blocks and moving them towards the front of the database file, other processes are busy allocating blocks - possibly newly freed ones - and using them. After a reorg, truncate then starts at the rear of the databases, and works towards the front of the database chopping pieces off and returning the space to the operating system. So, assuming that your database had free space available that truncate did not free, it means that as it worked from the end of the database towards the beginning, truncate found a local bit map and related blocks that were not 100% free. This can have two causes:
1. An active processes needed space and chose that local bit map to allocate space. Although processes have a bias for finding space near the front of a database file, there is a certain amount of randomness because there are multiple processes accessing the database and processes do not guarantee to use the available free space nearest to the front of the database file. If this is the case, and there is plenty of free space available, running the online reorg again, perhaps with less application load, would free more space.
2. The root block of a global variable is in one of the blocks of that last local bitmap. The performance related heuristics of the database engine assume that the root block of a global variable, once allocated, is never freed. If this is the case, then you would have to use DSE to perform surgery on the database (possible, but not recommended) - or extract the data and load it into a newly created database.
Regards
- Bhaskar
Bhaskar
Thanks for this information.
My third attempt to compact this database just finished a few moments ago and gave me the following successful result:
Truncated region: NODEVERSION. Reduced total blocks from to . Reduced free blocks from to .
So I think I can safely assume that the previous failure to truncate was caused by concurrent database activity.
Thanks very much for the detailed explanation.
George