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.

PatchMyPC HorizontalAD
Patch My PC Sponsored AD

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.

Location of Reporting Services Database Log ReportServer_log.ldf
Location of Reporting Services Database Log ReportServer_log.ldf

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.
Change the Recovery Model for Report Server DB
Change the Recovery Model for Report Server DB

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.

Shrink SCCM Reporting Services Log Database
Shrink SCCM Reporting Services Log Database

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.

Shrink SCCM Reporting Services Log Database
Shrink SCCM Reporting Services Log Database

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.

Shrink SQL Server Reporting Services log database

Leave a Reply

Your email address will not be published. Required fields are marked *

8 Comments

  1. Avatar photo Mayor Adam West says:

    You’re the man Praj. I get so much good info on System Center from you day after day. For that, I am grateful.

    Take care!

  2. Avatar photo Richard Gordon says:

    What do you do if this method does not shrink the file?

  3. I have SCCM + WSUS in the same server and SQL server on different server. I did not see Microsoft SQL Management Studio on these both servers. Which server has SQL Management Studio on it?
    Thanks

  4. Good reading. Thank you. In my production environment, the data file is 65MB, and my logfile is 900GB. Does this sound reasonable? I ran a shrink today, and reduced it by approx 100G using the exact method as to which you recommended in this article. Still seems large, but maybe this is not an unreasonable size.?