RM_ResourceFile table is too large
search cancel

RM_ResourceFile table is too large

book

Article ID: 217666

calendar_today

Updated On:

Products

IT Management Suite Client Management Suite

Issue/Introduction

The customer noticed that RM_ResourceFile table was too large (in his case over 41 million rows)

The customer started noticing this issue while trying to run a repair or reconfiguring 8.1 RU5 or upgrading from 8.1 RU7 ( "spSetProductUninstalled" stored procedure was taking too long to finish. See KB 217089). 

At this point, the customer is requesting to know if there is a way to trim this table (if we have a setting that will do this automatically) or if we have a query that we can use to do it manually before he needs to upgrade to 8.5 or 8.6.

Environment

ITMS 8.5, 8.6

Cause

Unknown. Possibly a very aggressive File Inventory was run against their computers.

Resolution

Dev provided the following suggestion for cleaning up the RM_ResourceFile table:


Understand how these files may be used on customer side and how they are collected.

Files may be created:
1. with software import and Software Inventory scan (it reports installed software components and files related to software), we cannot stop reporting those files in case if software scan is enabled and it is required, but we may cleanup old files that are not actual already
2. with File Inventory scan. it reports all files based on rules defined in Inventory task or policy, we can stop reporting such files by disabling or limiting File Inventory
3. other way (imported from console, created by some solution)

Analyze current set of files to understand how they appeared in table.

Firstly, identify files not collected by software inventory, not associated with other items and not collected by file inventory by the following query and clean up if they are found:
----------------------------------------------------------------------------------------------------------------------------
select count(distinct r.Guid) as [Unused Files] from RM_ResourceFile r

left join Inv_Installed_File_Details fd on fd.FileResourceGuid = r.Guid

left join Inv_Software_Key_Executable ke on ke._ResourceGuid = r.Guid

left join Inv_Software_Execution se on se._ResourceGuid = r.Guid

left join ResourceAssociation ra on ra.ChildResourceGuid = r.Guid

left join Inv_Monthly_summary ms on ms.FileResourceGuid = r.Guid

left join Evt_Application_Start appstart on appstart.FileResourceGuid = r.Guid

left join Inv_File_Details f on f._ResourceGuid = r.Guid

where fd.FileResourceGuid is null

and ke._ResourceGuid is null

and se._ResourceGuid is null

and ra.ChildResourceGuid is null

and ms.FileResourceGuid is null

and appstart.FileResourceGuid is null

and ISNULL(f.Protected, 0) = 0

-----------------------------------------------------------------------------------------------------------------------------

Then identify how many files are collected as part of Software Scan. Such files have association 'Software Component Contains File' (eabe86d3-aafd-487a-af63-5c95d7511af6)
we can check number of such files by query. Analyze what percent of all files belongs to this query result to understand how files were created.
---------------------------------------------------------------------------------------------------------------------------------
select count(distinct f.Guid) as [Files from Software Scan] from RM_ResourceFile f

join ResourceAssociation ra on f.Guid = ra.ChildResourceGuid

WHERE ra.ResourceAssociationTypeGuid = 'eabe86d3-aafd-487a-af63-5c95d7511af6'

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Key_Executable)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Execution)

AND f.Guid NOT IN (SELECT FileResourceGuid  FROM Inv_Monthly_summary)

AND f.Guid NOT IN (SELECT FileResourceGuid FROM Evt_Application_Start)

AND f.Guid IN (SELECT _ResourceGuid FROM Inv_File_Details WHERE ISNULL(Protected, 0) = 0)

------------------------------------------------------------------------------------------------------------------------

You may delete such files (except those that are detected as Key Executables or configured for metering - as they may be required for Software Usage Tracking configuration). Part of files will be reported again as soon as full software scan is executed, but we believe counts will be less even after executed software scan.

Then check files reported as part of File Inventory. If there are a lot of these files and customer do not need to have them for some audit, we would suggest to disable File Inventory from Inventory Policies and Tasks and cleanup those files for database:
----------------------------------------------------------------------------------------------------------------------------------
select count(distinct f.Guid) as [Files from File Inventory Scan] from RM_ResourceFile f

WHERE f.Guid IN (select FileResourceGuid from Inv_Installed_File_Details)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Key_Executable)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Execution)

AND f.Guid NOT IN (SELECT FileResourceGuid  FROM Inv_Monthly_summary)

AND f.Guid NOT IN (SELECT FileResourceGuid FROM Evt_Application_Start)

AND f.Guid NOT IN (SELECT ChildResourceGuid FROM ResourceAssociation)

AND f.Guid IN (SELECT _ResourceGuid FROM Inv_File_Details WHERE ISNULL(Protected, 0) = 0)

----------------------------------------------------------------------------------------------------------------------------------

Each of provided queries returns list of guids for file resources. To delete these files resources properly from NS it is required to add them to table dbo.ItemToDelete.
Run the following query for that. It unions all files from previous queries:

---------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ItemToDelete (Guid)

select distinct r.Guid as [Unused Files] from RM_ResourceFile r

left join Inv_Installed_File_Details fd on fd.FileResourceGuid = r.Guid

left join Inv_Software_Key_Executable ke on ke._ResourceGuid = r.Guid

left join Inv_Software_Execution se on se._ResourceGuid = r.Guid

left join ResourceAssociation ra on ra.ChildResourceGuid = r.Guid

left join Inv_Monthly_summary ms on ms.FileResourceGuid = r.Guid

left join Evt_Application_Start appstart on appstart.FileResourceGuid = r.Guid

left join Inv_File_Details f on f._ResourceGuid = r.Guid

where fd.FileResourceGuid is null

and ke._ResourceGuid is null

and se._ResourceGuid is null

and ra.ChildResourceGuid is null

and ms.FileResourceGuid is null

and appstart.FileResourceGuid is null

and ISNULL(f.Protected, 0) = 0

union all

select distinct f.Guid as [Files from Software Scan] from RM_ResourceFile f

join ResourceAssociation ra on f.Guid = ra.ChildResourceGuid

WHERE ra.ResourceAssociationTypeGuid = 'eabe86d3-aafd-487a-af63-5c95d7511af6'

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Key_Executable)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Execution)

AND f.Guid NOT IN (SELECT FileResourceGuid  FROM Inv_Monthly_summary)

AND f.Guid NOT IN (SELECT FileResourceGuid FROM Evt_Application_Start)

AND f.Guid IN (SELECT _ResourceGuid FROM Inv_File_Details WHERE ISNULL(Protected, 0) = 0)

union all

select distinct f.Guid as [Files from File Inventory Scan] from RM_ResourceFile f

WHERE f.Guid IN (select FileResourceGuid from Inv_Installed_File_Details)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Key_Executable)

AND f.Guid NOT IN (SELECT _ResourceGuid FROM Inv_Software_Execution)

AND f.Guid NOT IN (SELECT FileResourceGuid  FROM Inv_Monthly_summary)

AND f.Guid NOT IN (SELECT FileResourceGuid FROM Evt_Application_Start)

AND f.Guid NOT IN (SELECT ChildResourceGuid FROM ResourceAssociation)

AND f.Guid IN (SELECT _ResourceGuid FROM Inv_File_Details WHERE ISNULL(Protected, 0) = 0)

---------------------------------------------------------------------------

After that, please run NS.Quarter-Hour scheduled task from Task Scheduler, you will see messages in SMP log saying "Deleting queued items:" with percent indicator to show progress.

 

 

Note:

If you have a very large RM_ResourceFile table (with millions of rows to clean-up), inserting all those unnecessary rows to ItemToDelete may take a while and NS.Quarter-Hour scheduled task may not be able to clean them up in one round. 
You may want to:
1. Create a SQL-based task in the SMP Console
2. Modify the INSERT query to have only 4000 rows inserted at the time, something like this:

INSERT INTO ItemToDelete (Guid)

select distinct top (4000) r.Guid as [Unused Files] from RM_ResourceFile r

left join Inv_Installed_File_Details fd on fd.FileResourceGuid = r.Guid

left join Inv_Software_Key_Executable ke on ke._ResourceGuid = r.Guid

left join Inv_Software_Execution se on se._ResourceGuid = r.Guid

left join ResourceAssociation ra on ra.ChildResourceGuid = r.Guid

left join Inv_Monthly_summary ms on ms.FileResourceGuid = r.Guid

left join Evt_Application_Start appstart on appstart.FileResourceGuid = r.Guid

left join Inv_File_Details f on f._ResourceGuid = r.Guid

where fd.FileResourceGuid is null

and ke._ResourceGuid is null

and se._ResourceGuid is null

and ra.ChildResourceGuid is null

and ms.FileResourceGuid is null

and appstart.FileResourceGuid is null

and ISNULL(f.Protected, 0) = 0

3. Schedule to repeat the task to start 2 min before NS.Quarter-Hour scheduled task starts.
4. Let it run until this table is all clean of the unnecessary entries.

At least this way it can happen in the background without the customer waiting for the process to finish with his interaction.

Powered by Wolken Software