question

46591305 avatar image
0 Votes"
46591305 asked pituach edited

FIMSynchronizationService.mdf file size reduction related inquiry

The FIMSynchronizationService.mdf file size increases, so the capacity needs to be reduced. However, if clearruns cannot be executed due to insufficient DB capacity, is there any problem in operation even if I directly delete the data in the mms_step_object_details table of the FIMSynchronizationService DB?

windows-server-2016microsoft-identity-manager
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day ?!?

You description is not fully clear to me but I will try to make some guesses. If this not solve your needs then please provide more information and try to elaborate your scenrio.

If I understand correctly then you have a database data file with the name FIMSynchronizationService.mdf and you want to reduce the size of this file

even if I directly delete the data in the mms_step_object_details table of the FIMSynchronizationService DB

Delete data from the database (even drop all tables) will not reduce the size of the database file.

Behind the scene when you delete data from a table, it does not really delete anything from the disk and you can still read the data directly from the file. You should check my lecture about the internals of sql server table structure: https://youtu.be/XCmMRxkbpzM

In order to reduce the database data file you can use the command DBCC SHRINKDATABASE

For more information check the documentation: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql

With that said, in most cases, IT IS HIGHLY NOT RECOMEND TO SHRINK THE DATABASE FILES for many reasons!

Anyway, before you start play with the database side, you can use the following query in order to see what size of the file is actually not in-used

 SELECT 
      LogicalName = dbf.name
     ,FileType = dbf.type_desc
     ,FilegroupName = fg.name
     ,PhysicalFileLocation = dbf.physical_name
     ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
     ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
            - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
     ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
            - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
 FROM sys.database_files dbf 
 LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id 
 ORDER BY dbf.type DESC, dbf.name;
 GO


14150-image.pngRonen Ariely
Personal Site | Blog | Facebook | Linkedin


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.