SSRS mortally wounded

SQL Server Administration
1

Greetings,

I done messed up our Reporting sql server. now our L drive for logs is filling up real quick and affecting subscriptions etc with

ReportServerService__06_02_2021_10_10_21

files every 30 minutes and I cant seem to find a way for the service to self clean. Before I spin up a new reporting server and migrate everything anyone here ever dealt with this actual problem and how they got around it?

@jeffw8713 ? anyone?

Thanks!

2

Which log files are growing? Is it for the ReportServer or ReportServerTempDB databases?

If yes, the quickest fix for that is to set both of those databases to SIMPLE recovery. Unless you have some policy that requires SSRS to be point-in-time recoverable (and if you do then it should be reviewed).

Run this query in the ReportServer DB:

SELECT COUNT(*) FROM Subscriptions;

And see how many rows you have. There's also a Status column, you might want to GROUP BY that and see if you're getting a lot of failures.

If you find that you have way to many subscriptions in that table, there is a DeleteSubscription stored procedure you can use to clean up the ones you don't need. DO NOT delete from the Subscriptions table directly, always use the SSRS GUI or the stored procedures to do SSRS DB maintenance.

3

This is a .log files with errors from Reporting Services @robert_volk

thanks

4

Ah, forgot about those. They're safe to delete, except for the latest log file. I can't remember what the process is that creates a new sequence of files. Suggest deleting any log files more than 24 hours old, it's pretty easy to do with PowerShell.

If the log files are large (>50MB or more than 100K lines) you might want to analyze them and see if you're getting an excessive number of certain errors, that could point out a problem or bad configuration. (For instance, I had multiple errors when SSRS tried to initialize Oracle and Teradata data providers, which weren't installed. Commenting those providers out in the config file solved that).

I'd suggest doing that analysis before doing any kind of migration or move to a new SSRS instance, since doing so might not solve anything.

1 Like
5

Keeps erroring on "timeout occurred connecting to the Reporting database" but all reports work just fine. Will implement a clean up job but need to address the issue at root cause level as you recommend. thanks!

6

Found the issue.

  1. Ran a trace
  2. Found it was trying to delete a ghost sql job every second
    msdb.dbo.sp_delete_job @job_id 'DB5D4930-3760-4364-84D6-7232CF20053E'
  3. Since this is a ghost job that does not exist, the job fails to find it, writes to log file a totally unrelated error message
ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: 
, An error occurred within the report server database.  
This may be due to a connection failure, timeout or low disk condition within the database
  1. Created a sql job named it DB5D4930-3760-4364-84D6-7232CF20053E
  2. Process finds this job, deletes it
  3. L drive is happy, no more log files!
7

Thanks for posting exactly what you had to do.

As a bit of a sidebar, this is another "bullet" in the list of reasons why I have a basic hatred for any of the 4 letter words in SQL Server that begin with "SS".

1 Like
8

:laughing: :laughing: :laughing: