Last updated on August 1st, 2022 at 03:17 pm
I received this message when I tried to create a new folder on my SQL Server Reporting Services (SSRS) site: The transaction log for the database ‘ReportServer’ is full due to ‘LOG_BACKUP’. I knew immediately what it meant. The SQL Server log file for the ReportServer database was experiencing some issues. This blog post will talk about how I fixed the problem with my SSRS server and made the SSRS site healthy again.
By the way, in an upcoming blog post, I will show you how to prevent this problem from ever happening again.
First, connect to the site server using SQL Server Management Studio (SSMS). If you don’t have SSMS installed on your workstations, make sure to read my blog post, Where is SQL Server Management Studio (SSMS)?
Next, expand Databases and then right-click on ReportServer and select the Properties node.
When I looked at the ‘ReportServer’ database properties, I quickly noticed (on the General node) that the database was using a ridiculous amount of disk space!
On the Files node, I discovered that the log file was using all of the space. As a general rule, your SQL Server log file shouldn’t be more than 25% of the database size. My log file was 1,702 times larger than the database!
How do you fix this? I’m going to start by backing-up up my SSRS database and log file, then changing the SSRS database to a simple recovery model and finally I’ll shrink the log file size down to something more reasonable.
Backing-up the SSRS Database
In SSMS, right-click on the ReportServer database, select Tasks and then select Back Up…
Click on the Add… button.
Click on the ellipsis (…) button.
Provide a file name. Remember that the file location must have enough space to back up the database and then click on OK. In my example I’m using Junk.bak because I plan to delete this file after the backup is complete.
Click OK.
Click OK to start the backup process.
Click OK.
Backing-up the SSRS Log File
In SSMS, right-click on the ReportServer database, select Tasks, and then select Back Up…
Change the Backup type to Transaction Log. Use the same destination location as the database, and then click on OK to start the backup.
Click OK.
Changing the SSRS Database to a Simple Recovery Model
Back in Database Properties, on the Options node, change the Recovery model to Simple. Click on OK to apply this change.
Shrinking the Log File Size
Right-click on the ReportServer database, and then select Tasks | Shrink | Files.
Change the File type to Log. Select Reorganize pages before releasing unused space.
I’ll take the current log file size (141252.44) and subtract the available free space (140662.84) from it. That leaves me with 589.6, so to be on the safe side, I will shrink the file down to 700MB.
That’s still seriously huge, but it’s a lot better. Click on the OK button in order to start the process. Note: Due to the size of the log file, this process might timeout. If it times out, repeat the shrink process again.
Now that the shrinking process is complete, you must REPEAT the steps for backing-up the SSRS database and log file, as well as shrinking the log file size. You must do this in order to ensure that the database gets down to the proper size.
After repeating all of the steps a second time, the SSRS database and log file will be a lot smaller. They’ll be in the 10MB range.
Make sure, however, to give your log file at least 25% of the database’s size. In my case, the database used 85MB *.25 = 21.25MB and then I rounded it up to 25MB.
Now that looks at lot more reasonable! I now have a ton of free space on my hard drive too!
If you have any questions about how I fixed the problem with my SSRS server and made my SSRS site healthy again, please feel free to contact me @GarthMJ.