MSEndpointMgr

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:
33_1
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).
33_2
3. Right-click on ReportServer and select Tasks, Shrink and then Files.
33_3
4. Under File type, select Log. Use the default settings and click OK.
33_6
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.
33_7

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.
33_4
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.
33_5
All done! From now on this should not be a problem.

Resources

https://msdn.microsoft.com/en-us/library/ms190757(v=sql.105).aspx

Nickolaj Andersen

Chief Technical Architect and Enterprise Mobility MVP since 2016. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows devices and deployments including automation. Awarded as PowerShell Hero in 2015 by the community for his script and tools contributions. Creator of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService to name a few. Frequent speaker at conferences such as Microsoft Ignite, NIC Conference and IT/Dev Connections including nordic user groups.

18 comments

  • Unfortunately this fix has not worked for me. that log file has eaten up all of the free space on my drive.
    Are there any other considerations when switching to using the simple recovery model for this database?

      • Rebooted the whole server, just to be sure. I regained about 350MB of total free space

  • This was just what the doctor ordered. I kept noticing that the report logs was DB was taking up half of my Terabyte drive space. I thought that it may have been the reports themselves, but I was wrong. Thanks a bunch bro!

  • Thanks very much for your article! We had a 80GB file that now is 1.5GB thanks to your article.

  • You are awesome! Worked like a charm after I changed the recovery mode to simple. Why the default max size is 2 TB (considering it’s installed on a 200 GB partition…) is beyond me.

  • Thanks for this post. Our log file has grown to 550GB, I followed the steps to shrink and I see that it’s executing but the file size remains the same. Is there something else I can try?
    Thanks

    • I am having the same issue. I have run the shrink process, and I am not even sure it is working. When I click OK the window closes, and I do not have a progress window. My file size has not shrunk.

    • Hi Tim,
      If you right click on the ReportServer DB, selects Tasks -> Shrink -> Files. Choose the Log file type, what’s the value of the Available free space?
      Regards,
      Nickolaj

      • Hi Nickolaj,
        Thanks for getting back to me. 547879.36 MB (99%)is the amount of free space. I just ran the shrink again and now it’s down to 3200 kb. I did change the mode to Simple on Friday but I didn’t try to shrink afterwards so I guess everything is fine.
        Tim

      • Hi Tim,
        When running in Simple mode, you won’t have to think about the size getting out of hand for a very long time. I’m glad that it worked out!
        Regards,
        Nickolaj

      • Thanks for the pointer to Simple recovery model. The other SCCM DBs were set to Simple but ReportServer was Full, hence the need for 60 GB before switching. Only 5 MB now!
        -Russell

  • Thank you! I knew there was something i forgot. 250gb later I got the reminder.

Sponsors