Overview
You look at your MIMService DB and its 400GB. That’s a large size! First, lets discuss why the MIMservice DB will grow at all.
The MIMService is request based, everything is a request. Even when you want to cancel a request, it becomes a request to cancel a request so you just generated another request. Therefore, you can potentially get 1000’s of request in a day or even in an hr depending on what you are doing. Each request takes up a lot of DB space depending on the volume of information in each request. From my experience there are certain operations that can generate a lot of requests.
- Setting up group management. Depending on the number of groups, the size of the groups and how well it is done. For instance if you have 20k groups that is a lot of requests. Then lets say after setting it up you delete all of them you have doubled the huge mountain which you originally built.
- Setting up MIM Reporting. This one is a huge load especially when you already have all your groups and users loaded. Most people don’t touch MIM Reporting anymore so it should not be a problem.
- You have a huge number of users loaded into system and there is a lot of daily activity. You can find this in Colleges (especially community colleges which have a lot of turn over) and universities when they load their Alumni info into MIM.
Reducing the size
Every request have a lifespan that is determined by the Retention Period. This can be found under Resources/System Resource Retention Configuration. The default value is 30 days, so each request generated is stamped with a lifespan of 30days after which it expires and is cleaned out of the system. If you change the retention time it only means new requests will be stamped with the new retention time does not affect already generated requests.
There is a job in the MIM SQL scheduler called FIM_DeleteExpiredSystenObjectsJob, this job runs a store procedure called fim.DeleteExpiredSystemObjects at 3:30AM everyday and cleans up expired requests. Cleaning up expired requests can be resource intensive generating transaction logs that can exhaust you transaction log drive so once has to careful. This is why Msft has set a limit in that job of 20k expired requests to cleanup. You can increase that limit but I would watch your transaction log drive.
So if you have a large database then take a look at your tables and see which ones are the largest in size and number of items. You will find that the fim.ObjectvalueDateTime is large. This should objects to be expired and have expired. You have millions. 20k a day will not be enough to even make a dent.
The script below will tell you how many expired objects you have and how many about to expire
USE FIMService
GO
/*—————————————————————————–
* At this time, the only Object that FIM stamps an ExpirationTime on
* is a Request object. The Date stamped is a future date which defaults
* to 30 days from the day the Request has completed all of its processing.
* This date can be modified by the creation/update of this Out of box Object
* SystemResourceRetentionConfiguration and its Attribute [RetentionPeriod]
*
*—————————————————————————*/
DECLARE @currentTime DATETIME,
@expirationTimeAttributeKey SMALLINT,
@numberExpiredRequestObjects BIGINT,
@requestObjectTypeKey SMALLINT,
@retentionPeriodAttributeKey SMALLINT,
@retentionPeriodInDays INTEGER,
@systemResourceRetentionConfigurationObjectTypeKey SMALLINT;
SELECT @currentTime = GETUTCDATE(),
@expirationTimeAttributeKey = [fim].[AttributeKeyFromName](N’ExpirationTime’),
@requestObjectTypeKey = [fim].[ObjectTypeKeyFromName](N’Request’),
@retentionPeriodAttributeKey = [fim].[AttributeKeyFromName](N’RetentionPeriod’),
@systemResourceRetentionConfigurationObjectTypeKey = [fim].[ObjectTypeKeyFromName](N’SystemResourceRetentionConfiguration’);
/*—————————————————————————–
* This give you the Request Object Retention Period in days
*—————————————————————————*/
SELECT @retentionPeriodInDays = ValueInteger
FROM [fim].[ObjectValueInteger]
WHERE
[ObjectTypeKey] = @systemResourceRetentionConfigurationObjectTypeKey
AND [AttributeKey] = @retentionPeriodAttributeKey
SELECT @retentionPeriodInDays AS [RequestObjectRetentionPeriodInDays]
/*—————————————————————————–
* This give you the number of Requests that have been stamped with
* the ExpirationTime attribute.
*—————————————————————————*/
SELECT COUNT(*) AS [TotalRequestsThatAreFinalAndWillExpireInTheFuture]
FROM [fim].[ObjectValueDateTime]
WHERE
[ObjectTypeKey] = @requestObjectTypeKey
AND [AttributeKey] = @expirationTimeAttributeKey
/*—————————————————————————–
* This give you the number of Requests that have been stamped and the
* Expiration time is in the past. These Requests will be cleaned up by the
* FIM_DeleteExpiredSystemObjectsJob.
*—————————————————————————*/
SELECT @numberExpiredRequestObjects = COUNT(*)
FROM [fim].[ObjectValueDateTime]
WHERE
[ObjectTypeKey] = @requestObjectTypeKey
AND [AttributeKey] = @expirationTimeAttributeKey
AND [ValueDateTime] <= @currentTime;
IF(@numberExpiredRequestObjects > 0 OR 1 = 1)
BEGIN
SELECT N’There are ‘ + CAST(@numberExpiredRequestObjects AS NVARCHAR(19)) + ‘ Expired Request Objects in your system that exceed the configured retention period.’ +
‘ Make sure the SQL Server Agent Job [FIM_DeleteExpiredSystemObjectsJob] is enabled and’ +
‘ running successfully.’ AS [ExpiredRequestStatus]
END
ELSE
BEGIN
SELECT N’There are no Expired Requests in your system at this time.’ AS [ExpiredRequestStatus]
END
So you have to
- Run the job several times. You can schedule it to run every hour or every 3hrs. you have to be careful that maintenance jobs also run regularly because it can throw off your indexes if you remove a large number of records.
- You can amend the limit of 20k in the sp to something larger to say 40k or 60k. Test it gradually and find a safe higher number. A lot depends on how beefed up your SQL server is.
tlktechidentitythoughts 