Huge ReportServer database log

Today I noticed on two of my SCCM 2012 infrastructures that the ReportServer_log.ldf file was extremely large (>100GB).  This is quite concerning considering that the actual database is less than 256MB.  I have not yet investigated the root cause yet, but here is a workaround / solution to reclaim the disk space and prevent it from growing large again.

1. Open SQL Server Management Server and connect to the SQL Reporting Services Server (most likely your SCCM Primary server)
2. Change the ReportServer database Recovery Mode to “Simple” (right-click on the ReportServer database and select properties)
3. Manually backup the ReportServer database (right-click on the ReportServer, select Tasks, select backup, …)
4. Truncate the log file (start a new query and run “DBCC ShrinkFile (ReportServer_log, TruncateOnly“)
5. Configure the ReportServer database AutoGrowth to use a maximum file size (2048MB should be sufficient) (right-click on the ReportServer database and select properties, select Files, …)

You should now see that the log file is only a few MB.

References and additional guidance

• http://www.windows-noob.com/forums/index.php?/topic/6597-sccm-2012-issues-with-reportserver-logldf-size/
• http://www.scconfigmgr.com/2013/05/08/shrink-the-sql-server-reporting-services-log-database-used-by-configmgr/
• http://scexblog.blogspot.com/2013/07/reportserverlog-size-unexpected-growth.html
• http://www.sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/