Friday, September 20, 2013

How to shrink the SQL Server Reporting Services log database

Today I did support on a ConfigMgr 2012 environment with local SQL installation, which I prefer! This because the SQL Server Reporting Services (SSRS) log database ReportServer_log.ldf file was claiming almost complete diskspace (30GB). This is because of the Maximum File Size setting which is set to 2TB by default and no Maintenance Plan is active to shrink the file (by design). I will explain how to shrink the file in order to prevent that the logfile stay small.
Before shrinking the file

Start SQL Management Studio for that and expand Databases. Select the ReportServer database and rightclick on it. Choose Properties and Files and change Full recovery to Simple recovery first. Do a rightclick on the ReportServer database again and choose Tasks, Shrink and Files. Under "File type" select Log and click OK.
Have a look at "Available free space"

The ReportServer_log.ldf file should now decreased in size to 1-5MB instead of multiple GB's. In my situation the logfile was shrinken from 30GB to 5MB (after a few minutes). Much better that way, and no need to increase diskspace every time.

Hope you have the same experience as described here!

13 comments:

  1. Excellent Article

    Helped me fix my SCCM Log file size.

    ReplyDelete
  2. Thank you for your article, which was easy to follow and fix sccm log, which was more than 300 gb,

    ReplyDelete
  3. Great article, clear instructions that got the job done. Thanks!

    ReplyDelete
  4. Wow! This saved me 720GB!!

    ReplyDelete
  5. My database has 99% available disk space but it isn't shrinking the file. Thoughts?

    ReplyDelete
    Replies
    1. Maybe a restart of the SQL service will do the trick?

      Delete
  6. Excellent. it helps me to fix sccm log, Thanks

    ReplyDelete