SQLTeam.com | Weblogs | Forums

SSRS Database Migration Issues


#1

Hello,

Been working on some SSRS reports for different departments in a company where Excel is Lord. I am ok with Excel being Lord until you find out folks have 2 different versions of the same document and making critical decisions on erroneous data.
So we decided to implement SSRS with healthy adoption from folks. We tried things in dev for a while then we wanted to move the prod. There were a lot of reports so we decided just detach and restore ReportServer and ReportServerTempDb. All went well, reports went live with no down time. A couple of glitches with Subscriptions but once we got those fixed we are flying high...until. our C drive filled up with ReportServerServices_XXXX.log files. I disabled DefaultTraceSwitch using value of 0 but this puppy wont quit dumping 32MB files into C drive. Anyone has ever seen such an issue. I am thinking of starting from scratch
I have about tried every possible fix except for a patch
This is the error code.

e 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.;

THanks!


#2

Found the solution for this from
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dd78b8c3-15d9-466e-bcce-b0f7c2a9f9b4/ssrs-2016-logging-same-message-250-times-per-second?forum=sqlreportingservices

I created a SQL Profiler to see what was going on in the database as the text log files were not telling me anything useful. In the SQL Profiler I instantly noticed the following being run every second

exec msdb.dbo.sp_delete_job @job_name=N'1EE79B76-2C86-4579-83E3-18F713C669BD'

This was a non existing SQL Job. exec msdb.dbo.sp_delete_job does not check to see if the job exists before deleting it. And guess what happens?

The specified @job_name ('1EE79B76-2C86-4579-83E3-18F713C669BD') does not exist.

So this error was being logged every second!! hence the huge log files filling up the C drive. Microsoft I LOVE YOU!
So I created a job with that name to make it happy and voila! It deleted that job and log files are now 0KB.