Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two Windows Server 2008 servers set up, both running SQL Server 2008 R2. One is used as a primary DB, the other as a mirror failover (two website DBs + ASPState for sessions). Everything works great until one of the DBs becomes the primary after the server that it runs on has been rebooted. I get this error:

Invalid object name 'tempdb.dbo.ASPStateTempSessions'.

I am able to solve this by running the stored procedure ASPState_Startup (which executes ASPState.dbo.CreateTempTables), but only if the DB is the "principal" state. When a server has rebooted, it will always come back up with the DBs in a "Mirror / Restoring" state, meaning I cannot run that SP at startup.

Any suggestions on how I can get around this issue?

share|improve this question
It would seem to me that there is something wrong with the mirroring. Rather than putting on a a workaround, have you asked why you are getting the error in the first place? Try – Jim B Jun 11 '13 at 14:31
Mirroring is functioning okay; this is by design because tempdb is not and can not be mirrored. Can't hurt to get it over to – Jason Cumberland Jun 11 '13 at 15:36
up vote 1 down vote accepted

Try setting up a SQL Agent job that runs on each instance, every minute, and executes a script to look for the database in a not restoring state, check for the table and then execute the procedure if it doesn't exist. I took the object names from your question so it may need to be changed if they are off from your environment.

if exists ( select 1 from sys.databases where state_desc <> 'RESTORING' and 
[name] = 'ASPState' )
    if ( object_id('tempdb.dbo.ASPStateTempSessions') is null )
        exec sp_executesql @stmt = N'exec ASPState.dbo.CreateTempTables'
share|improve this answer
This did the trick - just one minor issue with the code. 'exec ASPState.dbo.CreateTempTables' should be N'exec ASPState.dbo.CreateTempTables'. Thanks! – Alec Sanger Jun 12 '13 at 18:00
changed the answer to reflect your feedback. – Jason Cumberland Jun 13 '13 at 2:58

Your Answer


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.