Shrink SQL Server Reporting Services log database

430

In this post we will see how to shrink SQL server reporting services log database. This post be helpful when you see your drive free space starts decreasing and you find out that report server log file is the one that is taking lot of disk space. I was working on SCCM troubleshooting an issue and I got an alert that disk space on my SCCM server was critically less. Upon checking this I found that ReportServer_log was consuming lot of disk space. When you install and configure SQL server reporting services, the ReportServer_log.ldf eventually grows up. This starts eating up the disk space on your server. The maximum file size of ReportServer_log is set to 2 TB by default. This setting could be changed as well, but we shall see that in another post.

How to Shrink SQL Server Reporting Services log database

Before we shrink the sql server reporting services log database, let’s check the file size. The ReportServer_log.ldf file is located at <drive letter>:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. In the below screenshot the log file size is not huge. If you are running SCCM in production, the log file size is usually huge. In my case the size of the reporting services log database is 7040 KB. Let’s see how much does it reduce to when we shrink it. Shrink SQL Server Reporting Services log databaseLaunch the Microsoft SQL server management studio. Expand Databases, right click ReportServer and click Tasks, click Shrink and click Files.

Shrink SQL Server Reporting Services log databaseChoose File type as Log from the drop down. Leave rest of the options as it is and click OK. Close the SQL Management studio.

Shrink SQL Server Reporting Services log databaseIn this example the size of the file is reduced from 7040 KB to 560 KB which looks really good. When i tried the same in SCCM server running in prod environment, I could see the reduction of space from 180 GB to 30 MB.

Shrink SQL Server Reporting Services log database