FIM2010 Troubleshooting: The FIM Database is growing too large and we're running out of disk space
General Information
Installing the FIM 2010 Synchronization Service Manager will create a database called FIMSynchronizationService.
Installing the FIM 2010 Service will create a database called FIMService.
These are 2 different databases which require some degree of size management. To get an idea of how much space they are taking by table, open SQL Server Management Studio, expand Databases, right-click on the required DB e.g. FIMSynchronizationService to bring up the pop-up menu then Reports -> Standard Reports select Disk Usage by Top Tables.
We have a TechNet article called Best Practices which describes the best practices for deploying and operating FIM 2010 including a useful section on SQL.
FIM Synchronization Service Database
Over time, the Sync Service DB will naturally grow in size as operations are run and data flows through it.
One particular issue I've come across several times in support is the growth rate of the table "mms_step_object_details".
Explanation
The table itself is written to when a run profile containing actions is executed. So for example, a Full Import with nothing new or changed to import, will not write to the table. However, a Full Import with 1 new object or an object with 1 update will create 1 record in the table. If there was 1 object with 2 updates, 1 record would be created. If there were 2 objects with changes, 2 records would get written to the table. Sync operations work slightly differently when writing to this table.
Testing Example
In a test environment, I created an AD MA connected to a directory with 1 OU containing 9 user objects and 1 group object.
I ran a Full Import which showed 12 Adds in the sync stats and added 12 rows to this table.
A Full Sync had 5 projections, 5 joins and 2 disconnectors, 10 objects with flow updates and the outbound sync to the FIM MA had 4 provisioning adds. This added another 37 records to the table.
A 2nd Full Sync making no changes added another 32 records.
A 3rd Full Sync again with no changes added another 32 records. Etc.
Making no further changes, if I just run a Full Sync every 10 minutes for 1 day that’s 144 runs 32 additional records added to the table which would equate to 4,608 per day and 838,656 within 6 months. And that’s just 1 operation with 10 objects in 1 OU with no changes being made. Include 1 or more additional Management Agents, their additional run profiles, a more complex Active Directory structure with changes being made regularly… you can soon see how this table could very easily and very quickly grow in size.
Real World Example
In one particular support scenario, I saw this DB grow from 130GB to 230GB and the table "mms_step_object_details" increase from 34 million records to 519 million records. Here, a 7-step sync cycle had been run every 10 minutes of every day for 6 months which itself added 183,456 records to the run Operations history.
Clearly this kind of growth rate could well pose disk space issues on the SQL server.
Operations / Run History
The Operations history in FIM Sync Service Manager should really not be allowed to exceed around 10,000 records otherwise there’s the potential of experiencing performance issues. It could be the list takes a long time to load for example, even to the point where the FIM Sync Service Manager UI appears to hang and be unresponsive.With the real world example above, this 10,000 records limit would be hit in under 10 days. To manage this, a windows scheduled task could be utilised to run a script to delete the history older than ‘x’ number of days.
Note: If this history grows too large, then deleting also has the potential to cause performance issues. Do not try to clear too much of the run history in one operation. When we clear runs from the run history, it’s contained in a single transaction. This means the larger the amount of data that is cleared, the more space the transaction log file on the Sync DB will grow. It would not be difficult to run out of disk space on the drive that hosts the transaction log file.
There are also some other considerations when deleting the run history. Make sure that there are no operations running in FIM and no SQL maintenance tasks running at the time. Clearing the run history is quite disk-intensive and so if there are other services running on the SQL server, it is possible clearing a large amount of run history will affect the performance. The TechNet Wiki article FIM-REFERENCE: How to clear the run history discusses this in more detail.
FIM Service Database
Installing the FIM Service DB will install SQL Server agent jobs that you can use to automate key maintenance tasks. These tasks are designed to improve performance of the FIM database or ensure consistency of sets and groups. SQL agent jobs execute scheduled administrative tasks, which contain one or more job steps. SQL Server can run an agent job on schedule, in response to a specific event, or on demand. The TechNet article Managing SQL Server Agent Jobs in FIM 2010 discusses this in more detail.