Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking into an issue with SQL Server 2008 Reporting Services. It has been working fine until yesterday morning. Now I notice that ReportServerTempDB keeps on going into Restoring... mode. I restored it to online mode with RESTORE DATABASE [ReportServerQ4TempDB] WITH RECOVERY.

The reports work fine after this and then after 10-15 minutes, then ReportServerTempDB goes back into Restoring mode. Report execution gives the following error:

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) For more information about this error navigate to the report server on the local server machine, or enable remote errors...

Any suggestions why it is doing that.

I have checked the following

  1. Memory - 50% available
  2. CPU - only 10% utilized
  3. Storage - 30% space remaining

There are multiple instance of SSRS running and SQL instance hosting the database for SSRS on the same server.

I have checked that AutoClose is not enabled for this database, and I have discovered that the database goes into Restoring... mode every hour, when our log backup job runs.

enter image description here

share|improve this question
1  
please show us exactly the command that the hourly log backup job is sending to SQL Server. Maybe it's also doing a restore without your knowledge. –  Aaron Bertrand Jul 31 '13 at 17:07
    
What is the backup solution that you're using? –  swasheck Jul 31 '13 at 17:37
2  
...and why is ReportServerTempDB in full recovery mode, and why are you performing log backups? You should consider switching it to simple and removing it from the log backup maintenance. –  Aaron Bertrand Jul 31 '13 at 17:44
2  
Also are you using log shipping on this instance? Any chance someone thought it would be a good idea to have ReportServerTempDB participate in log shipping, and the timing of the log shipping happens to coincide with your local log backups? –  Aaron Bertrand Jul 31 '13 at 17:56
1  
I agree.... I am changing it to Simple now. Hopefully won't see the issue again. I want to thank @swasheck, Aaron Bertrand, mrdenny for taking your time to help me with this. I appreciate it. –  Rizwan Aug 1 '13 at 18:11

1 Answer 1

up vote 7 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.