After recently upgrading a development environment from FIM 2010 to FIM 2010 R2 SP1, I decided to clear all objects out of the FIM Portal to give myself a clean development environment.
So first, I ran various PowerShell scripts to clear out the users and groups (making sure not to delete my administrator users). Then, I did a Full Import on the FIM MA – at which point I realised I had a whole heap of orphaned EREs.
“Not to worry”, I said, “I’ll just go and run [debug].[DeleteOrphanedRulesByType]”. This handy SQL Stored Procedure appeared on the FIMService database in FIM 2010 build 4.0.3594.2:
Issue 4
Some ExpectedRuleEntry objects and DetectedRuleEntry objects in FIM 2010 can become “orphaned” over time. When aDetectedRuleEntry object is not referenced in the DetectedRulesList of any object in the system, that object is determined to be orphaned. Similarly, when an ExpectedRuleEntry object is not referenced in the ExpectedRulesList of any object in the system, that object is also determined to be orphaned.
These orphaned objects have no functional impact on FIM. However, over time, these orphaned objects can cause a decrease in performance for both FIM operations and Sync operations that are related to FIM, such as import or export by using the FIM MA.
A pruning stored procedure, [debug].[DeleteOrphanedRulesByType], was added to the [debug] namespace of the FimService database. This stored procedure must be run separately for the DetectedRuleEntry object and the ExpectedRuleEntry object. The stored procedure also has a “reportOnly” mode, and this mode can be used to determine the presence and number of orphaned DetectedRuleEntry and ExpectedRuleEntry objects in the system.
The @ruleType parameter expects one of the following well-known values:
- N’Detected’ for DetectedRuleEntry objects
- N’Expected’ for ExpectedRuleEntry objects
To determine the number of orphaned objects in the system, run the stored procedure in “reportOnly” mode as follows.
DECLARE
@deletedRulesFound BIT;
EXEC [debug].[DeleteOrphanedRulesByType] @ruleType=N'CHANGE_ME', @reportOnly=1, @deletedRulesFound=@deletedRulesFound OUTPUT;
To loop through and actually delete orphaned objects in the system, run the stored procedure as follows. @deletionLimit=1000 instructs the procedure to stop when it has deleted 1,000 objects. If there are more than 1,000 orphaned objects in the system, either run the procedure multiple times (recommended) or increase the deletionLimit value.
DECLARE
@deletedRulesFound BIT,
@startDateTime DATETIME,
@endDateTime DATETIME;
SELECT @deletedRulesFound = -1;
WHILE @deletedRulesFound <> 0
BEGIN
SELECT @startDateTime = CURRENT_TIMESTAMP;
EXEC [debug].[DeleteOrphanedRulesByType] @ruleType=N'CHANGE_ME', @deletionLimit=1000, @reportOnly=0, @deletedRulesFound=@deletedRulesFound OUTPUT;
SELECT @endDateTime = CURRENT_TIMESTAMP;
SELECT @startDateTime AS [StartTime], @endDateTime AS [EndTime], @deletedRulesFound AS [WereDeletedRulesFound];
END
But now it’s gone, along with some of the other stored procedures which it referenced. So, where did it go?
In the meantime, there are a few options available to me:
- Create a set called “Orphaned EREs” and attach a “Delete object” custom workflow activity to a transition-in MPR related to that set. This is a solution originally put forward on the FIM Technet Forums by Bob Bradley (a colleague of mine at the time) back in 2010, prior to the release of FIM 2010 build 4.0.3594.2.
- Use the [debug].[PurgeObjectsOfType] stored procedure to delete all the EREs in the system. As this is a development environment, and I’ve already cleared out the objects, this is viable. I would not use this solution in a production environment.
So, away we went with option number 2… of course, now I’m getting “Stopped-Server” when I try to do a Full Import on the FIM MA from the FIM Sync Service. Note to self… never go against one of Bob’s suggestions! Next time I will definitely be going the Set/MPR/WF route.
Leave a Reply