Re: Log Shipping Role Reversal

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: kmkrause2 (kmkrause2_at_discussions.microsoft.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 06:31:04 -0700

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:

> 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.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
> >
> >
> >