Some users may have issues creating a community account. For more information, please click here.

FNMS Inventory Computer and History deletion scripts

FNMS Inventory Computer and History deletion scripts

  • Owner: Nicolas Rousseau
  • Solution Type: Backend SQL Script
  • Flexera Product & Version: FNMS on Prem
  • Environment:
  • 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.

Labels (2)
Was this article helpful? Yes No
No ratings
Comments

Hello Nicolas,

is it necessary to execute both queries or only one of them?

 

regards,

Matthias

Hi Matthias, There are two types of cleanups, you can decrease the size of your ComplianceHistory_MT table and keep older Inventory Manager computers or cleanup on the two side. How large is you ComplianceHistory_MT table? Please contact me: nrousseau@flexera.com.

Thanks, 

My ComplianceHistory_MT  is 800GB and is a series problem from a storage and cost perspective.  I am happy to see this article as I opened a ticket with Flexera some time ago and was told there is no method to clean it up.  

You may want to mention you will want your database to be in Simple backup mode prior to starting this cleanup as you will likely overload your transaction logs if you are logging.

Thanks Damon, you are totally right, using "Simple Recovery Model" is a must, even outside of this cleanup. I have seen customer with FNMS Compliance production database collapsing at first (large) inventory import. Best practice is always to use simple recovery model. FNMS is not a Business Critical application. One backup a day with not way to recover fully within the day is totally acceptable.

Hi Nicolas

Our FNMS Compliance database is using "FULL" Recovery model. What is the benefits of setting FNMS Compliance database using "SIMPLE" Recovery model? Getting better performance for inventory import and reconciliation?

Thanks

Is it possible to share the scripts is a .sql file?

I am getting the following errors when attempting to run Clean Up Inventoried Computers and had similar errors on the Compliance History cleanup scripts.

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'on'.
Msg 137, Level 15, State 1, Line 30
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 40
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar variable "@IncludedIDs".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@IncludedIDs".

Version history
Revision #:
2 of 2
Last update:
‎Sep 27, 2021 04:42 AM
Updated by:
 
Contributors