Rebuilding ReportServerTempDB

Whist reviewing various logs on SQL Servers across the estate I was greeted with the following error on one of our SQLExpress instances this morning:

The application domain WindowsService_22 failed to initialize. Error: 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. —> System.Data.SqlClient.SqlException: Database ‘ReportServer$SQLEXPRESSTempDB’ does not exist. Make sure that the name is entered correctly.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.<ExecuteNonQuery>b__0()
   at Microsoft.ReportingServices.Library.SqlBoundaryWithReturn`1.Invoke(Method m)
   — End of inner exception stack trace —
   at Microsoft.ReportingServices.Library.Storage.WrapAndThrowKnownExceptionTypes(Exception e)
   at Microsoft.ReportingServices.Library.SqlBoundaryWithReturn`1.Invoke(Method m)
   at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Library.ConnectionManager.EnsureDBCmptLevel()
   at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread(Object firstStart).

Sure enough ReportServer$SQLExpressTempDB did not exist.

At this point I was faced with a number of ways to recreate the database:

  • Restore from backup.
  • Re-attach the database files.
  • Create the database from scratch.

 

In this instance I went for option 2 as the files were still valid, but knowing how to re-create the database is pretty handy for your DR documentation as you may not have the first two options available to you. The steps for re-creating ReportServerTempDB are amazingly simple:

  1. Create the database, for a default instance this will be ReportServerTempDB, for a named instance this will be ReportServer$InstanceTempDB
    (remembering to size accordingly).
  2. In the new database create the database role RSExecRole.
  3. Assign the users of RSExecRole from ReportServer into the new RSExecRole in the new database.
  4. Run the script CatalogTempDB.sql – on my local build this was in “C:\Program Files\Microsoft SQL Server\MSRS10.SQL01\Reporting Services\ReportServer”, however this will differ depending on how SSRS was installed in your environment.
    (remember to run this script in the context of the ReportServerTempDB).