FNMS Inventory Computer and History deletion scripts
- Owner: Nicolas Rousseau
- Solution Type: Backend SQL Script
- Flexera Product & Version: FNMS on Prem
- Development Effort: 1 days
- Implementation Days: 0.1 Day
- Pre-Requisites: DB access with read write
- Disclaimer: Read Disclaimer
Deleting compliance computer records with a simple "DELETE FROM ComplianceHistory_MT WHERE DATEADD (dd, 95, HistoryDate) < GetDate()" could take days and eventually cause locks if other activities happen (inventory import for instance). The two approaches below offer a solution to that challenge.
One approach is to schedule a SQL server agent that will run every day from 10:00 AM to 5:00 PM (typically out of the inventory import time). Will select the top 5 million records... deleting by batches of 50K, with a commit every 50K records, to avoid a growth in log files. This is the first attached document.
Another approach that is way more efficient (extremely fast) is attached in the second script: this approach makes sense if the cleanup must be massive and impact potentially several billions of records. The approach is the following:
- Determine which ComplianceHistoryID corresponds to “3 month ago”. This ID will allow filters with way better performance.
- Export all “to be conserved” ComplianceHistory records:
- Less than 3 month old (old)
- More than three month old
- Related to contracts, purchases and Licenses.
- Truncate the ComplianceHistory Database (which will reset the complianceHstoryIDs)
- Re-import all conserved records.
- DROP the “To Be Conserved” History records temp table.
The interest of the script are the following
- It "reads" the ComplianceHistory_MT table columns (schema has varied over time) before "constructing" the queries based on the table schema. The script applies to any version of FNMS.
- The proportion of interesting records is very often way smaller than records to delete. Keeping the db and truncating it is more efficient than creating an empty one feeding it with records, dropping the big table and renaming the new table. There a triggers, indexes, constraints (to be renamed) that are making this option complex
- ComplianceHistoryID are reset with a truncate.
MAKE SURE YOU USE THE SQL SIMPLE RECOVERY MODEL ON FNMSCompliance. This will save you from db log file's growth troubles. This model should always be used on FNMS databases.
The attached document also provide a script that deletes from FNMS Inventory records, 1000 by 1000 with a commit every loop to avoid log files to grow.