ToDelete

5 years ago

Hello Oliver,

Deleting compliance computer records could take days and eventually cause locks if other activities happen (inventory import).

One approach is to use the script above and 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 scriptare 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.

and you get for free an FNMS Inventory Computer cleanup script

Expand Post

1 of 7

nrousseau1
5 years ago
Hello Oliver,
Deleting compliance computer records could take days and eventually cause locks if other activities happen (inventory import).
One approach is to use the script above and 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 scriptare 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.
and you get for free an FNMS Inventory Computer cleanup script
Expand Post

Related Topics