RESTORATION OF ReportServer and ReportServerTempdb

Hi,
I just want to share one of my experiences as DBA. One day my boss gave me a task of restoration of ‘Reportserver’ and ‘ReportServerTempdb’ databases. This is in SQL Server 2008R2. What I did is I tried to restore the database and I got an error saying that “The database is already in Use”. So I thought there might be other sessions are open on this database. So I ran sp_who2 stored procedure and kill all  the sessions which are connecting to ‘Reportserver’ database and try to restore the database as usual. But this time also I faced the same problem.

So I thought this time I will take the database in single user mode and try to restore the same. I failed in this attempt also. What I realized after some time was “Reporting Services” are running and this is stopping me to restore the database. I stopped this “Reporting Service” and restored the two databases with in one minute

After few days I got an error on "ReportServerTempdb" database the error details are as below.
"An error occurred with in the report server database. This may be due to a connection failure,timeout or lowdisk condition with in the database(rsReportServerDatabaseError) the transaction log for database "ReportServerTempdb" is full.To find out why space in the log can not be reused,see the log_reuse_wait_desc columns sys.database.

In order to troubleshoot the above issue I followed the below instructions. Before going into details let me explain about the "ReportserverTempdb" database.

Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server.Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.
If you back up the temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents.
If you delete the temporary database, you can create a new database, and then run the Catalogtempdb.sql script to add the table structure. The temporary database must have the same root name as the primary report server database. And the Catalogtempdb .sql script available in the below path
 \Program Files\Microsoft SQL Server\MSRS10. < instancename > \Reporting Services\ReportServer folder.
Now I can explain about the troubleshoosing stpes. First I ran the below script

DBCC SQLPERF('LogSpace')
Please find the results

And I also found that the Recovery model of a ReportServerTempdb is showing in 'FULL'. But this is not necessary and I changed this to 'SIMPLE'. After changing that to Simple log has been reduced to

Now the reports are available for access.

 

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server