< Blog

What Is the Problem with My SSRS Server?

By Garth Jones

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.

Problem with My SSRS Server-Error Message

This blog post will talk about how I fixed this problem 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.

Problem with My SSRS Server-Properties

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.

Problem with My SSRS Server-General 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!

Problem with My SSRS Server-Files Node

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

Problem with My SSRS Server-Database-Back Up

In SSMS, right-click on the ReportServer database, select Tasks and then select Back Up…

Problem with My SSRS Server-Database-Add

Click on the Add… button.

Problem with My SSRS Server-Database-Ellipsis 

Click on the ellipsis () button.

Problem with My SSRS Server-Database-OK 

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.

Problem with My SSRS Server-Database-OK-2

Click OK.

Problem with My SSRS Server-Database-OK-3 

Click OK to start the backup process.

Problem with My SSRS Server-Database-OK-4 

Click OK.

Backing-up the SSRS Log File

Problem with My SSRS Server-Log File-Back Up

In SSMS, right-click on the ReportServer database, select Tasks, and then select Back Up…

Problem with My SSRS Server-Log File-OK

Change the Backup type to Transaction Log. Use the same destination location as the database, and then click on OK to start the backup.

Problem with My SSRS Server-Log File-OK-2 

Click OK.

Changing the SSRS Database to a Simple Recovery Model

Problem with My SSRS Server-Simple Recovery Model-OK

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

Problem with My SSRS Server-Shrinking-Files

Right-click on the ReportServer database, and then select Tasks | Shrink | Files.

Problem with My SSRS Server-Shrinking-File Type 

Change the File type to Log. Select Reorganize pages before releasing unused space.

Problem with My SSRS Server-Shrinking-700MB

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.

Problem with My SSRS Server-26MB 

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, please feel free to contact me @GarthMJ.