Defragmenter helps you analyze your data files for statistics that may explain loss of performance. High statistics for file size and percentage of fragmented, unused, and not in order can explain the loss of database performance.
Every database is different, so making recommendations that apply to everyone is impossible. If you do not see performance improvements after defragmenting, then the problem likely lies elsewhere and requires a different diagnosis and solution.
Based on documentation using the Zen Control Center Defragmenter can be done while people are using System Five. It will negatively impact database performance while analysis and defragmenting are occuring.
Accessing Defragmenter
In Zen Control Center, select Tools > Defragmenter.
Defragmenter GUI
The following illustration shows the Defragmenter GUI.
Click on the dataset you want and drag and drop into the Watch List tab.
*** DO NOT SELECT ALL ***
Select 10-15 at a time and click on Analyze. I found clicking Select All and clicking Analyze made Zen appear hung and took a lot longer to complete.
Continue until all tables are analyzed.
Defragmenting.
2 approaches.
Defragmenting all
Like we did with analyze, select sections, select 10-15 and click on Defragment.
Defragmenting recommended.
Defragment any table that meets any of the following criteria.
Analysis | Greater Than or Equal To |
Fragmented | 15% |
Unused | 15% |
Out of Order | 5% |
** Note that sometimes It cannot improve one or more tables, but it does its best.
E.g. Stoktran.btr selected, then click on Defragment.
StokTran size now is smaller, no longer fragmented, no more unused records and down from 100% out of order to 11%.
When Defragmenter Cannot Be Used (from Zen documentation)
In some cases, Defragmenter cannot be used or will be cancelled by database activities of higher priority.
• Defragmentation of files on clients is not currently supported. Files can be defragmented only on the local server where the tool runs.
• The disk space needed to defragment a file is displayed in the analysis results. Defragmenter requires free disk space equal to the size of the file, plus a small amount for defragmenting operations. Files undergoing high write activity may require more space.
• If free disk space is too low, then the engine denies attempts to start defragmentation and returns error code 126. If space becomes low on a disk volume while defragmentation is executing there, the engine cancels the defragmentation of files on that volume and returns 126.
• If defragmentation starts but then the disk volume where defragmentation is executing becomes full, the engine cancels defragmentation activity on that volume and returns error code 18.
• Use other ZenCC tools to make changes to indexes and metadata, including:
• Creating, modifying, or deleting indexes
• Modifying a schema, such as creating, modifying, or deleting a column
• Deleting databases, tables, or data files
• Changing database, table, or file names
• Setting or clearing owner names
• Creating, modifying, or deleting bound databases
• Backup Agent operations cannot be performed on a file undergoing defragmentation. To perform defragmentation, you must first exit from Backup Agent.
• Continuous operations for data backup cannot be performed on a file undergoing defragmentation because their higher priority cause defragmentation to be cancelled. To perform defragmentation, you must wait for these operations to finish.
• Defragmentation is not currently supported for a server engine in an environment where VSS has performed backup operations
• If you need to alter data file properties, such as page size, compression, or file version, use Tools > Rebuild instead of Defragmenter.
• Defragmenter stops and exits automatically if it finds corrupted or erroneous records. In these cases, use Rebuild to recover the data. Freshly rebuilt files do not need defragmenting.
• For DataExchange users: Defragmentation does not change the system data and key used by DataExchange. After defragmenting, you do not need to run the table synchronization and check tool dxsynctables.
