Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 dba.stackexchange.com –  Jim B Jun 11 '13 at 14:31
1  
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 dba.se. –  Jason Cumberland Jun 11 '13 at 15:36
add comment

1 Answer

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' )
begin
    if ( object_id('tempdb.dbo.ASPStateTempSessions') is null )
    begin
        exec sp_executesql @stmt = N'exec ASPState.dbo.CreateTempTables'
    end
end
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
add comment

Your Answer

 
discard

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.