Jameel, One possible thing to look for is the actual file that is taking up the most space. If it is the transaction log, it is possible that you have recovery mode set to full and have not performed a backup in a long time. I actually had an instance of FIM 2010 where FIM service DB was 70+ GB, yet I had 2000 users in portal and almost no groups or custom objects. It turns out that a backup on transaction log had not been run since October and it had just kept adding to it. I didn't have enough space to perform full transaction log backup since it would have required another 70+ GB. I changed backup mode to recovery, then was able to later shrink files, it restored almost all of the 70 GB back to me.

Appreciate all your answers. As Glenn & Carol pointed out, it turns out even after the expired system objects are deleted SQL doesnt free up the disk space. The database needs to be shrinked to recover disk space. For more details refer to: http://fimguru.com/2011/01/fim-service-database-usage-report/Thanks & Regards, Jameel Syed Principal Consultant, fimGuru - Your window into simplified identities jameel.syed@fimguru.com - http://www.fimguru.com

If you're just looking at the file size of the database then there may be a lot of empty space in it. My (limited) understanding of SQL is that it never shrinks a DB file automatically, but keeps the free space there for future use. It is possble to shrink a DB file but it's not considered a best practise for performance reasons.http://www.wapshere.com/missmiis

Talk to a SQL DBA about it. There is a way to shrink the DB file through the GUI but I've never had much success that way. Instead a DBA would use a script to do it, presumably using this command: http://msdn.microsoft.com/en-us/library/ms190488.aspx http://www.wapshere.com/missmiis

Carol, Yes, the file size is what I was looking for. The size of this file hasn't changed a bit even after agressively deleting all the expired system objects. Currently I am at 105GB with just 15,000 users for the FIMService database. Are you saying most of this 105GB is empty space? Its kind of hard to digest that. Looking at a comment which Darryl made on this post, it seems that there is a way to prune the database of completed objects. Will appreciate some clarity around how to make the FIMService database maintanable for an enterprise which has a really high turnover. Thanks & Regards, Jameel Syed Principal Consultant, fimGuru - Your window into simplified identities jameel.syed@fimguru.com - http://www.fimguru.com

Hi, Whats the best way of pruning FIMService SQL Database of completed objects like, requests, workflows etc? We have had the DeleteExpiredSystemObjects SQL job running at a very aggressive rate. It seems to have done the job as the number of request objects returned in a search has dropped down significantly. However due to some reason that has not translated into a drop in the size of the database. It seems as if the database still needs to be pruned periodically in addition to running the SQL job. Is there any recommendation on periodically pruning this database? Thanks & Regards, Jameel Syed Principal Consultant, fimGuru - Your window into simplified identities jameel.syed@fimguru.com - http://www.fimguru.com