Jul 19 2010

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:

Create the database, for a default instance this will be ReportServerTempDB, for a named instance this will be ReportServer$InstanceTempDB
(remembering to size accordingly).
In the new database create the database role RSExecRole.
Assign the users of RSExecRole from ReportServer into the new RSExecRole in the new database.
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).

Richard Douglas

Richard is a Systems Consultant for a vendor of SQL Server solutions where he specialises in SQL Server providing solutions and system health checks to organisations across the EMEA region. Richard has recently taken on the responsibility of being Editor in Chief of the community site SQLServerPedia which provides articles on SQL Server and a blog syndication service, other duties see him regularly presenting webinars to both US and EMEA audiences. He is also a keen member of the SQL Server community, he founded and runs a PASS affiliated chapter in the UK (www.SQLStudy.org) and is on the organising committee for a national event called SQLRelay. Read more about Richard.

More Posts - Website

Follow Richard here:

By Richard Douglas • DR, Server Admin, SQLServerPedia Syndication, SQLSyndications, SSRS • 3 • Tags: DR, Server Admin, SSRS, System Databases

Pingback: Reporting Services: regenerando ReportServerTempDb : Sergi & Replace
http://www.facebook.com/profile.php?id=653266982 Aaron Kempf

hey I tried your ‘rebuild ReportServerTempDB’ method last night.. I had someone at work stop the service, kill the ReportServerTempDB_1.log (or whatever the path WAS) because it had grown huge.. (probably not being backed up). We do a couple hundred thousand reports a month at work (things like shipping labels, invoices, and we have a LOT of subscriptions, etc).

So I tried your method last night.. didn’t really work, I was getting missing table errors from SSRS, and from a visual inspection, I assumed we were missing these tables:

TempCatalog
TempDataSets
TempDataSources

Note, It could be that this is because I’m on SQL 2008 R2, not SQL 2008– it could be something that simple. Also, the other alternative is that I could download SQL 2008 R2 Service pack and look for some sort of newer version of this file CatalogTempDB.sql, but I just didn’t want to deal with the drama.

I played around with it copying other tables, I kept on having collation issues.. so I burned some time looking at discrepancies and converting between collations, I don’t know what I was thinking.. wasted about an hour.

I finally said ‘screw it’ and I actually tried restoring a backup copy of my desktop’s ReportServerTempDB and restored it over the top of my production server’s (re-created using your method) ReportServerTempDB and this ReportSererTempDB been working like a charm. I chose the edition from my desktop that matched the edition on the server, and I’m really glad I had the cojones to try that– otherwise, I’d probably still be rebuilding this reporting services installation.

Looks to me like restoring ReportServerTempDB from a different instance is preferable to recreating this database, because it actually works. Again, I could have looked for a newer version of the .SQL script, but I just – I can’t find anything using Windows Search, I really don’t know who invented this product (and why Google desktop search went the way of the dodo). Oh yah, I -do- know why Google Desktop Search went the way of the dodo- it failed because it simply didn’t allow you to index _ALL_ extensions equally!!! I’m a programmer, and I want to search for weird extensions.. and Google desktop only indexed a couple dozen extensions… That drove me absolutely crazy.

Either way, I’m going to keep a spare copy of this database lying around, or I’ll figure out what I can do to that ReportServerTempDB without -actually- backing it up..
http://SQL.RichardDouglas.co.uk/ Richard Douglas

Thanks for your comments. I’m sorry that this did not work for you. I’m afraid that I do not have a 2008 R2 instance to try this out on. If I do gain access to one and have the time I will do an update for R2.

Rebuilding ReportServerTempDB

Richard Douglas

Archives

Pages

Rebuilding ReportServerTempDB

Richard Douglas

Popular Posts

Tags

Archives

Pages

Categories