How to Shrink SCCM Reporting Services Log Database

In this post, I will show you how to shrink SCCM reporting services log database located on SQL Server. With a few easy steps, you can shrink the SQL Server reporting services log database (ReportServer_log.ldf) used by ConfigMgr.

SQL Server Reporting Services (SSRS) is one of the requirements for the SCCM reporting service point. When you install SSRS for SCCM, there are two databases that are installed: ReportServer and ReporServerTempDB. When you run a report, the Reporting Services component connects to the Configuration Manager site database to retrieve data.

A report server keeps track of information about how the server is running and what its status is by sending logs to different places. The execution log is a SQL Server view stored in the report server database. Check out all the important SCCM log files useful for troubleshooting common issues.

The size of the report server database log file taking up most of the disk space is one of the most common problems SCCM administrators run into. Recently, I was troubleshooting an Configuration Manager issue, and I got an alert that disk space on my SCCM server was critically low. Upon checking this, I found that the ReportServer_log.ldf file was consuming a lot of disk space. The only solution was to shrink the SQL Server reporting services log database.

Note: With SSRS configured for SCCM or reporting in general, by default the ReportServer database Recovery model is enabled as FULL. Which means that all activity in the ReportServer database will get logged to the transaction file. Most important, the Transaction log file can grow with time.

Location of ReportServer_log.ldf

The ReportServer_log.ldf file is located at <drive letter>:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA. After you install and configure SQL server reporting services for ConfigMgr, the ReportServer_log.ldf file eventually grows up. The default max size value of the ReportServer logfile (ReportServer_log.ldf) is 2 TB.

From the below screenshot, we see the ReportServer_log file is taking up roughly around 500 MB of disk space. In SCCM production environments consists of huge number of clients, the size of this file may go over 300 GB and sometimes even more. If you don’t shrink or reduce SQL Server reporting services log database, it may consume more space on the disk and eventually the drive will be full.

Change the Recovery Model for Report Server DB

1. Log onto SQL Management Studio and connect to the SCCM instance.
2. Expand Databases and right-click on ReportServer.
3. Go to Options and change the recovery model from Full to Simple.
4. Click OK to save the changes.

How to Shrink SCCM Reporting Services Log Database

Let’s go through the steps to shrink the size of reporting services log database for SCCM.

• Launch the Microsoft SQL server management studio.
• Connect to the Database engine.
• Expand Databases, right-click ReportServer and select Tasks > Shrink > Files.

On the Shrink File – ReportServer_log window, click the drop-down for File type and from the list of options, select “Log“. Choose the Shrink Action to Release unused space. You can leave rest of the options as it is and click OK.

Close the SQL Server Management Studio tool after making the above changes.

This should reduce the size of the ReportServer_log.ldf file to a more modest size, you may have to repeat the shrink step two or three times for it to complete successfully.

From the below screenshot, we see the size of the file is reduced from 7040 KB to 560 KB which is really useful. When I used the steps on the SCCM server running in production, the reporting services database size shrunk from 180 GB to 30 MB.