Many of the features, such as the CMG, in ConfigMgr Current Branch require adding an Azure Active Directory tenant into the ConfigMgr console. If you’ve done so you may notice that your site database has grown significantly. In my case my lab environment, which has fewer than 10 clients, had reached 20Gb in size. This can the impact performance of ConfigMgr due to auto growth of the database. Plus, there’s really no point to waste your storage space with excessively large database files and backups.
I first noticed this issue on 1806 however I am unsure which versions are or are not affect by this issue. If you’ve noticed that your database files and backups are significantly larger than expected open SQL Management Studio and run the report “Disk Usage by Top Tables.
The problematic table is the dbo.SCCM_Audit table. This table stores audit information for certain critical configurations for the environment. Ideally there should not be constant configuration changes meaning the table shouldn’t be growing excessively. Notice in the screen shot below that my SCCM_Audit table is almost 8Gb in size.
This is resulting in the mdf file in my database being 12.5Gb in size.
Once you’ve confirmed that you are affected by this issue create a backup of your database before running the following delete statement on your ConfigMgr database. (yes, making changes to the database is unsupported if you are not comfortable making the change I suggest that you contact CSS)
delete SCCM_audit where tablename=N’AAD_Tenant_Ex’
Go grab some coffee or maybe lunch because this is going to take a while to complete. In my lab environment on a server with 64Gb of RAM, 8 CPU’s and all solid state disks it took 45 min. One it’s complete you will see a message something like (88,743 row(s) affected) in the results pane.
Next you will want to reclaim all of that wasted space. In SQL Management Studio right click you ConfigMgr database, highlight Tasks, Shrink and select Files.
Select Reorganize pages before releasing unused space. In the “Shrink file to” box enter a size which is 10% larger than the number displayed in Minimum is xxxx MB. The additional 10% is to alleviate auto growth of the database.
After the shrink has completed re-check the size of the .mdf file. I was able to reclaim approximately 65% of the space originally consumed.
Once you’ve completed the deletes from the database and shrank the database you will notice that your SQL log file is now very large. This is due to logging all of the deletes.
To resolve this run the following command to shrink the log file. Note that the database needs to be in simple recovery mode, if it’s not you should put it in simple recovery mode before running this.
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (CM_PJM_log, 1);
Afterwards you will see that you’ve recovered all of the space from the log file.