Environment

Problem Statement

FIM Reporting utilizes System Center.  There is a table in System Center Service Manager database called the Entity Change Log.  This is where the new changes to be dealt with are initially loaded. The grooming job is scheduled for every 48 hours by default, and is designed to run in one transaction. If there are a great many changes coming in, say, for example, a ROPU (Run-On Policy Update)  set such that it always gets triggered and the set is very large, say, All Users, the database can be overwhelmed and the grooming never succeeds.

• The SQL Server Stored Procedure in question is called p_DiscoveryDataPurgingByBaseManagedEntity
  • This Stored Procedure is a SCSM procedure defined in the ServiceManager database.
  • This Stored Procedure is called by dbo.p_DiscoveryDataPurging proc, which is called by the dbo.p_DataPurging stored procedure
  • This Stored Procedure seemed to be causing the transaction log growth, given it was trying to delete all the target records in a table in one transaction.  With 120M records at one point in one of the tables, it couldn't grow the transaction log large enough to handle that.  At least, that's my interpretation of what is going on.

Resolution

• Fix the portal to address the issue with tons of changes generated
  • This involved removing the ROPU setting that was touching the All Users Set.  By touching the All Users Set it generates lots of changes which could affect the performance of FIM Reporting.
• Modify the Stored Procedure p_DiscoveryDataPurgingByBaseManagedEntity to run in batch mode so it can complete a batch, run another and so on.
  • Modification of a number of the delete statements within the Stored Procedure allowed the data purging to proceed without the exponential growth of the transaction log.  Over the course of several days, the number of records to be purged was steadily reduced, to the point where we are at now, where the data purging seems to be keeping ahead of the record growth.

Additional Information

Here is a good blog on Run-On Policy Update (ROPU).