Purging Old Extender Window Records
Extender does not provide a native method to purge or archive old Extender Window records. As Extender data accumulates over time, it can negatively impact performance, particularly in SmartLists and GP reports that reference Extender fields.
This article explains why this behavior occurs and outlines a controlled SQL-based approach for safely removing old Extender Window records using a defined cutoff date.
Symptoms
Customers may report one or more of the following:
- SmartLists run noticeably slower when Extender fields are included
- Dynamic GP reports that previously performed well now take significantly longer to complete
- SQL Server performance issues related to large Extender tables
- Continuous growth of Extender data with no built-in cleanup option
Cause
Extender stores all window data in a limited number of shared tables and does not separate records by transaction status (such as work, open, or history). As a result, Extender data continues to grow indefinitely.
Only the Extender Window Key Values table (EXT01100) contains created or modified date information. The related Extender data tables do not store date fields, which makes date-based cleanup more complex than with standard Dynamics GP module tables.
As data volume increases, SmartLists and GP reports that join Extender data must scan larger datasets, leading to gradual performance degradation.
Resolution
A controlled SQL script can be used to delete Extender Window records older than a specified date. The script is designed to:
- Identify qualifying Extender records using date information from EXT01100
- Remove associated Extender data using key-based relationships
- Execute deletions in manageable batches to reduce SQL transaction log impact
- Run a final routine to clean up any orphaned records
This approach allows historical Extender data to be safely removed while minimizing risk to system stability.
Script Configuration Parameters
The following parameters are defined at the top of the script and can be adjusted as needed.
CutoffDate
Specifies the date threshold used to determine which records are deleted.
Example: “2024-01-01T00:00:00”
All Extender Window records older than 01/01/2024 will be purged.
BatchSize
Controls the number of Extender Window key records deleted in each batch.
- Default value 10,000
- The script commits after each batch
- Smaller batch sizes reduce system load
- Larger batch sizes may complete faster but increase transaction log usage
PauseMilliseconds
Defines the pause interval between batches.
- Default value: 500 milliseconds
- It is generally not recommended to set this below 250 milliseconds
Script Execution Behavior
During execution, the script provides progress output for each batch, including:
- Number of Extender Window Key records deleted
- Number of related Extender data records removed
- Cumulative total of deleted data records
After completion, the script reports:
- Total execution time
- Total number of unique Extender Window Key records deleted
Important Note
⚠ WARNING: Always perform a full SQL backup of the company database before running this script.
Additional considerations:
- The script does not filter by specific Extender window
- All Extender records older than the configured
CutoffDateare permanently deleted - Deleted data cannot be recovered without restoring from backup
- Execute the script during off-hours or a scheduled maintenance window
- This script does not remove the associated Dynamics GP data from the database
Recommendations
- Test the script in a non-production environment first
- Start with a conservative cutoff date and batch size
- Monitor SQL transaction log growth during execution
- Consider periodic purging to prevent future performance issues
More Information
This procedure is intended for experienced Dynamics GP or SQL administrators who understand Extender table relationships and SQL Server maintenance best practices.
If selective purging (such as deleting records for a specific Extender window only) is required, the script will need to be customized and thoroughly validated.
You may download this script by clicking here: Purge Extender Window Records by Date
Please contact us if you have any questions on purging old Extender Window Records.
