Shrink the SQL Server Reporting Services log database used by ConfigMgr 2012

So today I came across an issue with my Microsoft Endpoint Configuration Manager, formerly System Center Configuration Manager (SCCM) database server running low on disk space.

On investigation I found the ReportServer_log.ldf file had grown to over 320GB in size!

It would appear this is a known issue and some simple configuration changes to your database setup can resolve this.

Change the recovery model
1. Log onto SQL Management Studio and connect to the SCCM instance
2. Expand Databases and right click on ReportServer (your name may vary depending on your installation)
3. Go to Options
4. Change the recovery model from Full to Simple
5. Click OK to save the changes

Shrink the file
1. Log onto SQL Management Studio and connect to the SCCM instance
2. Expand Databases and right click on ReportServer (your name may vary depending on your installation)
3. Select Tasks > Shrink > Files
4. Under File type select Log and click OK
5. This should reduce the size of the LDF file to a more modest size, you may have to repeat the shrink step two or three times for it to complete successfully

Change the Maximum File Size
1. Log onto SQL Management Studio and connect to the SCCM instance
2. Expand Databases and right click on ReportServer (your name may vary depending on your installation)
3. Select Properties
4. Select Files
5. In the Database files field, scroll until you see the Autogrowth column.
6. Click on the ellipsis button (…) for ReportServer_log (again this name may vary slightly depending on your installation)
7. Change the value of the Restrict File Growth (MB) field to a suitable amount for your environment.
8. Click OK to save the changes

This should now resolve your issue and not see it reoccur.

As always if you’d like to reach out and speak to me about any of the above please get in touch via Twitter @TechieLass