Im having an issue with the SQL Agent job FIM_DeleteExpiredSystemObjectsJob.  It fails everyday and I can't figure out what is going on, but this is whats displayed in the log file:

Date                      01/08/2011 3:00:00 AM

Log                         Job History (FIM_DeleteExpiredSystemObjectsJob)

 

Step ID                 2

Server                   Server1

Job Name                            FIM_DeleteExpiredSystemObjectsJob

Step Name                         FIM_DeleteExpiredSystemObjectsStep

Duration                              00:00:04

Sql Severity                        14

Sql Message ID                 50000

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

 

Message

Executed as user: BAH\svc_ashb_sql08. Reraised Error 2627, Level 14, State 1, Procedure DeleteExpiredSystemObjects, Line 96, Message: Violation of PRIMARY KEY constraint 'PK_ExpiredObjectKeys'. Cannot insert duplicate key in object 'fim.ExpiredObjectKeys'. [SQLSTATE 42000] (Error 50000).  The step failed.

This is an error that is fixed by applying update 1 for FIM 2010.

http://social.technet.microsoft.com/Forums/en-US/ilm2/thread/b21e08a5-d2be-4bf1-a53f-248b5a9cc6e3

http://support.microsoft.com/kb/9788

Opper I got an email notification that you replied asking a followup question but I don't see it here. Nonetheless, I will answer it

You asked:

To be clear, I am at version 4.0.2592.0.  What is the version # after Update 1?

The answer is that update 1 is 4.0.3531.2

Guys,

Could someone who has installed Update 1 (or any of the later hotfixes), please reply with the actual sql for the latest version of [FIMService].[fim].[DeleteExpiredSystemObjects] stored procedure.

Due to circumstances outside of my control I am unable to download and install this update and urgently need to fix the foreign key problem mentioned above.

I know this will not be supported by Microsoft, but I have a bloated database which needs tidying up ASAP.

Thanks

Trevor, I can e-mail it to you if you'd like..

Cheers

I have a problem with FIM_DeleteExpiredSystemObjectsJob too. In my case it is seemingly running correctly, but the requests aren't being cleared.

The SQL agent job history says it's succeeding, and there are no messages in the event log.

I've now got 33 days-worth of requests but a retention interval of 15 days. Previously this has worked fine (for about a year). It's surely only a matter of time before this system does itself [possibly irreparable] damage.

Hi Simon,

Do you have a very large number of request objects? I had a similar problem and a huge number of request objects. Turns out the stored proc fim.DeleteExpiredSystemObjects has a request limit parameter of 20,000. I found by runnin gthe FIM_DeleteExpiredSytstemObjectsJob multiple times it eventually cleared everything out.

Hope that helps.

Thanks,

Sami

I have about 48,000 requests. But running the job doesn't clear _any_ of them out.

Best way to troubleshoot this is to run the same stored procedure that the job runs, but run it by hand in the query window in SSMS. See how many rows it affects.

Remember that it clears out requests that have been committed or otherwise completed for just longer than the retention period. I don't think it applies in this case but it is also good to remember that the request is stamped with the retention period in use at the time of its creation/completion. In other words if I have a retention period of 30 days, make some requests and 20 days later I update the retention period to 5 days that only impacts new requests, not existing ones. So new requests will clear out in 5 days, which is 5 days sooner than my original requests made 20 days previous.

Hi Simon,

Do you have a very large number of request objects? I had a similar problem and a huge number of request objects. Turns out the stored proc fim.DeleteExpiredSystemObjects has a request limit parameter of 20,000. I found by runnin gthe FIM_DeleteExpiredSytstemObjectsJob multiple times it eventually cleared everything out.

Hope that helps.

Thanks,

Sami

Agree with Sami. However wanted to add that, this limit of 20,000 objects can be increased to upto 100,000. I was in a similar situation and had to run this job agressively to normalize the size of the

Hi,

If you search for the requests, do they have the right ExpirationTime? (Kinda builds on what David mentioned about what happens when you change the request retention policy.)

Thanks,

Sami

• Edited by SamiVV Tuesday, October 04, 2011 6:21 PM typo

 

So, after closer inspection it turns out that the maintenance job is working. But there are still requests (not many fortunately) hanging around that I wouldn't expect. I wouldn't expect a request dated 5th September to have an expiry time of 20th October for example - not with a retention time of 15 days. And I can see a few of these. Also quite a few with no expiration time at all - what happens to them?

If any can answer those questions, please let me know.

But most requests - when I advance through a few pages - are actually OK.

Thanks for the help everyone. 

Hi,

I am also facing the same issue when FIM_DeleteExpiredSystemObjectsJob runs it freezes the whole system then i have to reboot. 

The job again runs and fail.

My FIM Version is FIM 2010 R2.

Update 1 which is accepted as an answer above only applies to FIM 2010 not FIM 2010 R2.

Is there any fix available for FIM 2010 R2 too.

Thanks

Vik