Hi all, I would like to start a discussion about a problem I had. A customer generated a couple of million requests within a week or two. I tried to clean it up with several approaches that fail because the FIM Service timed out whatever I made. 1. Trying to upload the expiration date and enforce to expire the objects that the SQL agent would clean up 2. Trying to delete them via PowerShell (doesnt make sense because for each requests you log another "Delete Request"-Request) 3. Trying to create an MPR with a set... whatever; didn't work either because FIM timed out as well Last conclusion; delete within the database directly. What i did, I check what the SQL clean up does and which stored procedures are called to clean up the expired objects. The SP FIM.DeleteExpiredSystemObjects was the one I was looking for. So, I broke up the whole procedure into smaller parts and determined where it checks the date value and uncommented it. After that I ran it step by step to ensure that everything went well. The procedure copies all objects which are in the filter to [fim].[ExpiredObjectKeys] and afterwards deletes wherever it has a reference. If that has been done and also validated, it deletes all objects from [fim].[ExpiredObjectKeys]. That solved it and the system got rid of the mass of requests. Thanks god, that this was just a Q-System, but however TechNet couldn't support me and I even dont know whether this is supported or not. Would you recommend a different approach if you would run in such an issue? Are you doing things directly on the DB (service or sync)? Is that something which should be somewhere in technet? Thanks for your feedback, Moritz

Hi, Personally, I would not mess with the database if there is any other option. Even if you work with the standard stored procedures, there still is a chance you mess up you FIM configuration. The only queries that I occasionally execute on the database is to clear all connectorspaces and the metaverse on FIM Sync. It can be very usefull for in development scenario's.Pieter de Loos - Consultant at Traxion (http://www.traxion.com) http://fimfacts.wordpress.com/

You can change the request retention time to 1 day. Once the requests are deleted, you can set it back to whatever feasible for you. Here is an article that tells you how to do that: http://www.identitychaos.com/2009/11/fim-2010-rc11-adjusting-request-object.html

Touching data in the DB is not recommended at all and with the arrival of FIM 2010 R2, it should get more complicated due to the auditing feature. You could change the request retention time to 1 day. Once the requests are deleted, you could set it back to whatever feasible for you. Here is an article that tells you how to do that: http://www.identitychaos.com/2009/11/fim-2010-rc11-adjusting-request-object.html

Setting the request retention time to a day or two is good advice for a development environment. Getting into the database is not supported. Depending on your auditing requirements though, that might not be feasible. In that case, proper SQL maintenance is another route. One thing I didn't notice in your post: There's a SQL job called "FIM_DeleteExpiredSystemObjectsJob" that has cleared things out for me in the past--but it's only going to delete the expired objects, so it might not have helped in your case. In my case, the job wasn't getting triggered for some reason, so I had to kick it off manually. After that, it started working on schedule. (No idea why.)

Setting the request retention time to a day or two is good advice for a development environment. Getting into the database is not supported. Depending on your auditing requirements though, that might not be feasible. In that case, proper SQL maintenance is another route. One thing I didn't notice in your post: There's a SQL job called "FIM_DeleteExpiredSystemObjectsJob" that has cleared things out for me in the past--but it's only going to delete the expired objects, so it might not have helped in your case. In my case, the job wasn't getting triggered for some reason, so I had to kick it off manually. After that, it started working on schedule. (No idea why.)