Log Shipping Role Reversal

We're implementing log shipping and have run into problems
when we attempt to reverse server roles.
The error occurs when we run the sp_change_secondary_role.
We receive a "sqlmaint.exe" error. If the stored procedure
is run 3 or 4 times it will occasionally succeed but the
role reversal is not successful even then.
Has anyone had any experience with this?
Thanks,
JohnKen,
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/defaul...7&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 neede
d
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
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k[/ur
l]
> 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 be
en
> 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 thi
s
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 nee
ded
> to to a detach/attach sequesnce on the database before running the seconda
ry
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:
>