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

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

Nickolaj Andersen
Principal Consultant and Enterprise Mobility MVP. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows deployments and Automation. In 2015 Nickolaj was awarded as PowerShell Hero by the community for his script and tools contributions. Author of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService and a frequent speaker at user groups.

(3544)

comments
  • J.Brandell
    Posted at 17:37 July 23, 2013
    J.Brandell
    Reply
    Author

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

    • Nickolaj
      Posted at 06:43 July 24, 2013
      Nickolaj
      Reply
      Author

      You’re welcome! 🙂

      /Nickolaj

  • Tim McGowan
    Posted at 15:34 November 1, 2013
    Tim McGowan
    Reply
    Author

    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

    • Jeri
      Posted at 19:35 November 1, 2013
      Jeri
      Reply
      Author

      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.

      • Nickolaj
        Posted at 11:31 November 2, 2013
        Nickolaj
        Reply
        Author

        Hi Jeri,

        I’d advise to check out the recovery model and how to configure it: http://msdn.microsoft.com/en-us/library/ms189275.aspx

        It’s recommended to use the Simple recovery model with ConfigMgr. With the Simple recovery model you’d not have to deal with DB log file size.

        Regards,
        Nickolaj

    • Nickolaj
      Posted at 11:23 November 2, 2013
      Nickolaj
      Reply
      Author

      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

      • Tim McGowan
        Posted at 14:55 November 4, 2013
        Tim McGowan
        Reply
        Author

        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

        • Nickolaj
          Posted at 16:28 November 4, 2013
          Nickolaj
          Reply
          Author

          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

          • Russell
            Posted at 18:22 July 1, 2015
            Russell
            Author

            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

  • Justin
    Posted at 16:49 January 14, 2014
    Justin
    Reply
    Author

    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.

    • Nickolaj
      Posted at 19:27 January 14, 2014
      Nickolaj
      Reply
      Author

      Hi Justin,

      I’m glad that this was able to help!

      Regards,
      Nickolaj

  • hyperthread
    Posted at 01:10 August 28, 2014
    hyperthread
    Reply
    Author

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

    • Nickolaj
      Posted at 16:17 September 8, 2014
      Nickolaj
      Reply
      Author

      Hi,

      That’s just great! I’m glad that you found this article helpful.

      Regards,
      Nickolaj

  • Mike Speed
    Posted at 17:59 May 19, 2015
    Mike Speed
    Reply
    Author

    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!

    • Nickolaj
      Posted at 07:21 May 22, 2015
      Nickolaj
      Reply
      Author

      You’re welcome Mike!

      Regards,
      Nickolaj

  • Mark
    Posted at 17:05 July 20, 2015
    Mark
    Reply
    Author

    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?

    • Mike Speed
      Posted at 19:41 July 20, 2015
      Mike Speed
      Reply
      Author

      Did you restart your SQL services after you changed the report log file?

      • Mark
        Posted at 21:06 July 21, 2015
        Mark
        Reply
        Author

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

  • Leave a Reply