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?