Log Shipping Role Reversal

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
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...&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
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:

> 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...&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...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote: