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

Log Shipping restore problem

I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.Before you restore, you need to make sure there's no one in the database.
You will have to kill all user connections in the database, if there are
any. More info can be found at:
http://vyaskn.tripod.com/administration_faq.htm#q16
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Vic" <vduran@.specpro-inc.com> wrote in message
news:017e01c3d93f$dd6bc360$a401280a@.phx.gbl...
I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.

Log Shipping restore problem

I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.Before you restore, you need to make sure there's no one in the database.
You will have to kill all user connections in the database, if there are
any. More info can be found at:
http://vyaskn.tripod.com/administration_faq.htm#q16
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Vic" <vduran@.specpro-inc.com> wrote in message
news:017e01c3d93f$dd6bc360$a401280a@.phx.gbl...
I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.sql

Log Shipping Problem

I am implementing Log Shipping. I have the transaction logs copying to
the standby server and applying to the standby database with no errors.

However, I am having problems with the role change. I am using the
following article/link as my guide:
http://msdn.microsoft.com/library/d..._1_013_9t0p.asp

The DTS package that I am to create to copy the logins from the current
primary server to the current secondary server is not working.

The job that I created (that encompasses BCP Out, Copy File, and
Transfer Logins) fails at the Transfer Logins step. That is the step
that calls the DTS package. Even though the Copy File step shows as
'successful', the file is definitely not on the standby server.

Here is the error message from the Transfer Login step:
Executed as user: SERVERNAME\Administrator. DTSRun: Loading...
DTSRun: Executing... DTSRun OnStart:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun OnError:
DTSStep_OMWCustomTasks.OMWTransferLogins_1, Error = -2147467259
(80004005) Error string: Unspecified error Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 700 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0) Error
string: Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 700 DTSRun OnFinish:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun: Package execution
complete. Process Exit Code 1. The step failed.

Both servers are running Server 2003. Both databases are SQL Server
2000 with SP3.

I have followed the steps in the article. Am I missing something? Or,
have I misinterpreted something? I would appreciate any help you could
provide.

Thanks in advance,
JennieI purchased Allan Hirt's "SQL Server 2000 High Availability" last
night. It has an entire chapter (72 pages!) devoted to log shipping. It
has slightly different instructions for setting up the
post-configuration tasks - as compared to the Microsoft article that I
had been following. His instructions work!

If you are implementing log shipping, I highly recommend this book!

Jennie

Log Shipping Concern

My department is thinking of implementing Log Shipping for a database. We
have a concern that, due to the nature of replication via log files, some
transactions may not execute properly on the secondary server and when this
occurs we will have essentially non-matching databases. Is this concern a
real issue and if so, how are such issues resolved?
Right now our situation allows us to dump and restore the database at any
time and take it offline if we have to. This dump and restore availability
ensures that we have two exact copies of the database with identical
footprints. Basically our concern is that we want to be assured an equal
concurrecy with log shipping.
Can anybody provide any insight?
Thanks,
Jeff
PS. I was not sure which group to post this in (I posted the same message
in this and in .Replication). Which group do Log Shipping questions belong
in?Log Shipping works but can be a pain in the neck.
the biggest problem we ran into was the occurence of "Non-Logged" Operations
in production.
this breaks log shipping quicker than you can blink.
Cheers,
Greg Jackson
Portland, OR|||Log shipping is mainly used for maintaining standby
Servers.
If you want to do logshipping for just only one database
in a server, make sure you are taking a right decision.
Compare the pros & cons of Replication and Log Shipping
and see whether log shipping is your right choice over
Replication. If you are more concerned about data
concurrency, check whether Replication solves your purpose.
Once log shipping has been implemented, it is relatively
easy to maintain. If Logshipping is implemented properly,
usually there wont be much issues.Its very robust.
I have encountered only the issues which is explained in
the articles below.
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/reskit/sql2000/part4/c1361.asp
This link may help you get better idea of Log shipping.
This is a webcast.
http://support.microsoft.com/default.aspx?kbid=821786
The issue you have mentioned about the non-matching
databases, I have not come across in my vast experience in
Logshipping or Replication. I think that,if the databases
on publisher and subscriber are in sync with every aspects
(contraints,data..), then this will not occur.
If I have understand correctly, then Replication is the
best and suits your purpose.
Hope this gives you some idea to start with.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>My department is thinking of implementing Log Shipping
for a database. We
>have a concern that, due to the nature of replication via
log files, some
>transactions may not execute properly on the secondary
server and when this
>occurs we will have essentially non-matching databases.
Is this concern a
>real issue and if so, how are such issues resolved?
>Right now our situation allows us to dump and restore the
database at any
>time and take it offline if we have to. This dump and
restore availability
>ensures that we have two exact copies of the database
with identical
>footprints. Basically our concern is that we want to be
assured an equal
>concurrecy with log shipping.
>Can anybody provide any insight?
>Thanks,
>Jeff
>PS. I was not sure which group to post this in (I posted
the same message
>in this and in .Replication). Which group do Log
Shipping questions belong
>in?
>
>.
>|||>the biggest problem we ran into was the occurence of "Non-
Logged" Operations
>in production.
>this breaks log shipping quicker than you can blink.
If you are in a production environment, you shouldn't be
doing non-logged operations. And under SQL 2K, this
really for the most part isn't possible. You use Full or
Bulk-Logged, and log shipping works. Simple prevents you
from making tran log backups at all.
The non-logged issue was more of a problem in SQL 7 and
earlier.