none
Very large FIMSynchronizationService database - any way to shrink? RRS feed

  • Question

  • I'm currently working with an instance of FIM where the FIMSynchronizationService database has grown to >200Gb and occupied all available disk space so that the sync service will no longer run. The reasons why this occurred are still being investigated but in the meantime I need to get this database down to a reasonable size so that I can restore at least some functionality. I can't simply perform a shrink operation because SQL Manager reports 0Mb free space in the database itself. The transaction log is much smaller (about the expected size).

    Short of detaching the database, moving it to a different/new partition and re-attaching (which will be difficult in the specific context) or reinstalling the sync service, are there any T-SQL commands that might help to free up some space so that a shrink operation will work? PowerShell?

    All suggestions appreciated!

    Tuesday, August 7, 2012 11:47 AM

Answers

  • The supported ways are via the FIM admin UI or WMI.  I've cleared the runstep and runstep_detail tables via direct SQL in test environments but don't recommend it in production.

    For a one-time pruning, you might try something like this:

    $wmi = Get-WmiObject -Class MIIS_Server -Namespace root\MicrosoftIdentityIntegrationServer
    $cutoff = get-date '1-aug-2012'
    $start = get-date '1-jan-2012'
    while ($start -le $cutoff) { $wmi.ClearRuns($start); $start = $start.AddDays(1); }

    Modify $start to roughly the date of the very oldest entry in the run history, and $cutoff to the date of the oldest entry you want to retain.

    Disclaimer: I'm a C# guy, so my Powershell is probably not entirely idiomatic.


    Tuesday, August 7, 2012 4:39 PM
  • Neon,

    I would verify that the Recovery model is set to 'Simple' as opposed to 'Full'. You can do this by going to SQL Server Management Studio, right clicking the FIMSynchronizationService DB and then going to options. If the dropdown is set to Full, change to Simple, this will free up space.

    This is one issue, the run history as brought up by Steve above is the run history, this can grown in a hurry and if it is not maintained, this can be a real problem. To confirm that run history is the culprit, check the size of the table mms_step_object_details. This table is probably enormous. You can reduce size by calling WMI methods using Powershell or .vbs files.

    Removing the run history takes some space in the transaction log so if you are that close to running out of disk space on the drive, you probably won't be able to do this yet; you might need to add temporary storage in order to reduce run history, then you can free up space.

    Wednesday, August 8, 2012 5:12 AM
  • All of the above suggestions are valid ... but on the off chance that your FIM server is actually a VM, I know that the latest virtualization technology in VMWare and HyperV support live disk size increases.  This would give you breathing space to begin to perform the suggested clean up tasks above ... worth asking the question I thought :).  Also, note that once you have switched from the FULL to the SIMPLE recovery model, you will need to manually shrink the log file yourself ... usually a couple of hundred MB is enough.  Also suggest you change the auto-growth option to grow the database in chunks of say 50 Mb instead of the default % growth, since this can have an enormous negative impact on SQL performance once you have solved the initial available disk space issue.

    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    Saturday, August 11, 2012 2:48 PM

All replies

  • So, what's taking up the space?

    If the log is using gobs of space, and the database is using Full transaction logging mode (which I believe is still the default), performing a database backup will allow SQL to trim it.

    If the data files are taking up most of the space, verify that you're regularly pruning old entries from the synchronization run history--at least weekly via script, or more frequently if you have lots of run steps or many changes.  If you're facing a huge number of run history entries, you'll want to delete them in small batches, as the deletes are of course transacted.

    Tuesday, August 7, 2012 3:36 PM
  • Thanks for the suggestions. One quick question if I may: Is it safe to clean out the FIMSynchronizationService history tables (in batches, as noted)? This may be a way forward for us.
    Tuesday, August 7, 2012 3:58 PM
  • The supported ways are via the FIM admin UI or WMI.  I've cleared the runstep and runstep_detail tables via direct SQL in test environments but don't recommend it in production.

    For a one-time pruning, you might try something like this:

    $wmi = Get-WmiObject -Class MIIS_Server -Namespace root\MicrosoftIdentityIntegrationServer
    $cutoff = get-date '1-aug-2012'
    $start = get-date '1-jan-2012'
    while ($start -le $cutoff) { $wmi.ClearRuns($start); $start = $start.AddDays(1); }

    Modify $start to roughly the date of the very oldest entry in the run history, and $cutoff to the date of the oldest entry you want to retain.

    Disclaimer: I'm a C# guy, so my Powershell is probably not entirely idiomatic.


    Tuesday, August 7, 2012 4:39 PM
  • Neon,

    I would verify that the Recovery model is set to 'Simple' as opposed to 'Full'. You can do this by going to SQL Server Management Studio, right clicking the FIMSynchronizationService DB and then going to options. If the dropdown is set to Full, change to Simple, this will free up space.

    This is one issue, the run history as brought up by Steve above is the run history, this can grown in a hurry and if it is not maintained, this can be a real problem. To confirm that run history is the culprit, check the size of the table mms_step_object_details. This table is probably enormous. You can reduce size by calling WMI methods using Powershell or .vbs files.

    Removing the run history takes some space in the transaction log so if you are that close to running out of disk space on the drive, you probably won't be able to do this yet; you might need to add temporary storage in order to reduce run history, then you can free up space.

    Wednesday, August 8, 2012 5:12 AM
  • I have also read somewhere that if you are doing PCNS password synchronization through FIM Synchronization Service target MA's that the password change history can cause the FIMSynchronizationService database to grow quite large.  Is this true, and if so, how does one go about clearing out the password change history?  Thanks.

    Friday, August 10, 2012 1:47 PM
  • The MIIS_Server WMI class also has a method "ClearPasswordHistory" that works the same as my Powershell example above.

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms697735(v=vs.85).aspx

    --Steve

    Friday, August 10, 2012 1:50 PM
  • Thanks Steve.  So, to be clear, this functionality is not part of the FIM Sync gui (FIM 2010) tool itself (like the Clear Runs history), and will need to write custom code for this?  I noticed from your link that it requires Mmswmi.mof (WMI, MOF, etc.).  This is all new to me, so would you have a link to some good documentation on some background information on WMI, MOF, etc. and how to actually implement this?  Thanks again.

    Friday, August 10, 2012 2:29 PM
  • I can't recall seeing an option to prune password change history in the FIM UI, but I also haven't worked in an environment where this developed into a problem.

    WMI is present on any Windows OS as a core feature, and you don't need to delve into the mysterious world of WMI/MOF/WBEM for this simple case.  Try the Powershell above first?

    Friday, August 10, 2012 2:59 PM
  • Thanks Steve.  So, I will look at using Powershell.  Thanks. 
    Friday, August 10, 2012 3:22 PM
  • All of the above suggestions are valid ... but on the off chance that your FIM server is actually a VM, I know that the latest virtualization technology in VMWare and HyperV support live disk size increases.  This would give you breathing space to begin to perform the suggested clean up tasks above ... worth asking the question I thought :).  Also, note that once you have switched from the FULL to the SIMPLE recovery model, you will need to manually shrink the log file yourself ... usually a couple of hundred MB is enough.  Also suggest you change the auto-growth option to grow the database in chunks of say 50 Mb instead of the default % growth, since this can have an enormous negative impact on SQL performance once you have solved the initial available disk space issue.

    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    Saturday, August 11, 2012 2:48 PM
  • Although this is an old post, we can always use more MIM documentation. After running the ClearRuns script I also had to manually shrink the FIMSynchronizationDB. It took several hours, but it finished. Thanks for posting.

    -Hank Foss

    Tuesday, August 27, 2019 10:51 AM