Why are you running a log backup job on [ReportServerQA5TempDB]
? It should be in simple mode.
From MSDN:
ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.
and
Use the simple recovery model to backup the reportservertempdb database.
Now the real question becomes, "What are you using for your backups?" Check to make sure that it's not changing the recovery model and that ReportServerTempDB is still in SIMPLE
recovery model.
SELECT name, recovery_model_desc
FROM sys.databases;
I wonder if your solution is trying to switch back and forth between recovery models and is failing somewhere along the way. I have a vague recollection of this having happened to me in the past.
Try changing the recovery model to Simple and removing it from your hourly log backup job.
ALTER DATABASE ReportServerQA5TempDB SET RECOVERY SIMPLE;
GO
SSRS Planning a Deployment would be a good article to read through.
You can read specifically on the SSRS databases here. As it states:
A report server is a stateless server that uses the SQL Server
Database Engine to store metadata and object definitions. A Reporting
Services installation uses two databases to separate persistent data
storage from temporary storage requirements. The databases are created
together and bound by name. By default, the database names are
reportserver and reportservertempdb, respectively.
You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).
The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.
To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.
Best Answer
I’m not 100% sure but I think there is no supported way to rebuild these.
If you have backups of these databases then just restore them. If not you can try creating another empty database and then try to synchronize structure and data and see if that can help.
I’d suggest you use some third party tools for this.