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

When you install ConfigMgr 2012 and add Reporting Services (SSRS) in order to utilize all the built-in reports, you may not think of that the ReportServer_log.ldf file will eventually eat up all the disk space on the volume where it resides. That is because of the Maximum File Size setting which is set to 2TB by default. In this blog post we’ll be shrinking the file and changing some values in order to prevent that the ReportServer_log.ldf will eat up all of the disk space on your SQL Server.

Overview

• Shrink the ReportServer_log.ldf
• Change the Maximum File Size

Shrink the ReportServer_log.ldf

This was how it looked like when all of the disk space was eaten up by the ReportServer_log.ldf in our environment:

To prevent this from happening, there’s a few easy steps that needs to be configured.
1. Open Microsoft SQL Management Studio.
2. Expand Databases and select ReportServer (in this case I’m not using the default instance and that’s why it has $CM12 at the end, this may differ from your SQL Server).

3. Right-click on ReportServer and select Tasks, Shrink and then Files.

4. Under File type, select Log. Use the default settings and click OK.

5. The ReportServer_log.ldf file should now have decreased significantly in size. Open <drive letter>:\Microsoft SQL Server\MSSQL10_50.<instance>\MSSQL\Data and have a look. In this case the file was shrunken from 260GB to a couple of megabytes.

Change the Maximum File Size

Now that the ReportServer_log.ldf file is shrunken, we need to change the Maximum File Size setting from 2TB to a more suitable limit for the environment.
1. In Microsoft SQL Management Studio, expand Databases and select ReportServer.
2. Right-click on ReportServer and select Properties.
3. In the left pane, click on Files.
4. In the Database files field, scroll until you see the Autogrowth column.
5. Click on the ellipsis button (…) for ReportServer_log. Make sure you select the correct row.

6. Change the value of the Restrict File Growth (MB) field to a suiteable amount for your environment. I choose 100 000 since the volume is about 300 GB on the server. Click OK.

All done! From now on this should not be a problem.

Resources