Re: Log Shipping Role Reversal

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ron Talmage (rtalmage_at_prospice.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 12:00:54 -0700

Ken,

See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k

This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @terminate parameter to 1.

Hope this helps,
Ron 

-- 
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
>
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @db_name = 'ModelCopy',
> @backup_log = 1,
> @terminate = 1,
> @final_state = 3,
> @access_level = 1
>
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
>
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @db_name = 'ModelCopy',
> @do_load = 1,
> @force_load = 1,
> @final_state = 1,
> @access_level = 1,
> @terminate = 1,
> @keep_replication = 0,
> @stopat = null
>
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
>
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
>
> TIA,
> Ken
Quantcast