log shipping load failure

Hi guys,
In my lodg shipping setup i selected the option terminate
users in database. but if the database is open the restore
is failing with following error.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101:
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive
access could not be obtained because the database is in
use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG
is terminating abnormally.
is it normal?
as fars as i know with the above option selected it should
terminate the users and restore succesfully.
thanks
Biju
Why do you have connections open to the read-only DB?
What connections are open when this happens?
"biju george" wrote:

> Hi guys,
> In my lodg shipping setup i selected the option terminate
> users in database. but if the database is open the restore
> is failing with following error.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive
> access could not be obtained because the database is in
> use.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG
> is terminating abnormally.
> is it normal?
> as fars as i know with the above option selected it should
> terminate the users and restore succesfully.
> thanks
> Biju
>
|||yes i have opened sql query analyser
[vbcol=seagreen]
>--Original Message--
>Why do you have connections open to the read-only DB?
>What connections are open when this happens?
>
>"biju george" wrote:
terminate[vbcol=seagreen]
restore[vbcol=seagreen]
Exclusive[vbcol=seagreen]
LOG[vbcol=seagreen]
should
>.
>

log shipping load failure

Hi guys,
In my lodg shipping setup i selected the option terminate
users in database. but if the database is open the restore
is failing with following error.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101:
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive
access could not be obtained because the database is in
use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG
is terminating abnormally.
is it normal?
as fars as i know with the above option selected it should
terminate the users and restore succesfully.
thanks
Biju
..
Biju,
no, this is not normal. Can you double check that the option was enabled by
editing the maintenance plan. If it definitely is, then have a look at the
current activity window (locked objects, TSQL etc) on the standby server to
see which process is locking the database and what exactly it is doing. This
should give some ideas as to what the issue is.
Regards,
Paul Ibison
|||Hi paul,
yes i checked the option it is enabled.
>--Original Message--
>Biju,
>no, this is not normal. Can you double check that the
option was enabled by
>editing the maintenance plan. If it definitely is, then
have a look at the
>current activity window (locked objects, TSQL etc) on the
standby server to
>see which process is locking the database and what
exactly it is doing. This
>should give some ideas as to what the issue is.
>Regards,
>Paul Ibison
>
>.
>
|||Biju,
OK - have a look at the processes locking the database in the current
activity window (or sp_who, sp_who2, sp_lock etc).
If necessary do a trace.
Regards,
Paul Ibison

Log Shipping Failover

Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
PepsLog shipping pairs are at the individual database level. As such you
can change the log shipping roles of the primary & secondary for a
single DB if you like without affecting any other databases that happen
to be shipping their logs to or from the primary or secondary servers
(with sp_change_secondary_role, sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the logshipping
maint plan, RESTORE...WITH RECOVERY on the secondary and create a
logshipping maint plan (on the secondary) in the other direction)).
For more info see BOL:
How to set up and perform a log shipping role change
http://msdn.microsoft.com/library/d...r />
_9t0p.asp
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:

>Is it possible to failover a single or a selected number of databases
>onto a standby server? We're currently log shipping about 12 databases
>to a standby and I was wondering if it was possible to randomly
>failover onto selected dbs.
>My understanding of log shipping is that when the primary server goes
>down, the standby takes over. But, does that mean that all of the db's
>on the standby server now become the primary? Or, can failover occur on
>selected dbs?
>
>The reason I ask is because if one db on the primary, for example, was
>to go down, I'd like to be able to bring the particular db on the
>standby server online while I fix or restore what's on the primary
>server. Can it be done individually or is it an all or nothing
>scenario?
>
>Your personal advice or links is greatly appreciated.
>
>Thanks,
>Peps
>
>|||Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?|||Those sp_change... procs are in the msdb database. You will need to
either switch to that database before running those procs (with the USE
statement) or explicitly state the full object name when executing it,
for example
exec msdb.dbo.sp_change_primary_role @.db_name='MyDB', ....;
Personally I would do this all with T-SQL statements, however it may be
easier for you to do it with the GUI:
1) manually start the SQLAgent "backup" job on the primary,
2) manually start the SQLAgent "copy" job on the primary,
3) delete the maint plan on the primary,
4) restore that log backup on the secondary DB using the WITH RECOVERY
option,
5) delete the SQLAgent "restore" job on the secondary;
6) then, optionally, you can establish the log shipping in the other
direction
just by going through the maint plan wizard on the secondary and
specifying
the old primary as the new secondary.
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same passwords
as on primary) and that they're not orphaned. Up to you.
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above):
How to set up and perform a log shipping role change (Transact-SQL)
http://msdn.microsoft.com/library/d...r />
_9t0p.asp
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:

>Thanks, Mike.
>I tested this on one of the db's but I received an error about not
>finding the store procedure 'sp_change_primary_role.'
>Any ideas?
>
>

Log shipping failover

Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.

My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?

The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?

Your personal advice or links is greatly appreciated.

Thanks,
PepstheSpinel (thespinel@.gmail.com) writes:
> Is it possible to failover a single or a selected number of databases
> onto a standby server? We're currently log shipping about 12 databases
> to a standby and I was wondering if it was possible to randomly
> failover onto selected dbs.
> My understanding of log shipping is that when the primary server goes
> down, the standby takes over. But, does that mean that all of the db's
> on the standby server now become the primary? Or, can failover occur on
> selected dbs?
> The reason I ask is because if one db on the primary, for example, was
> to go down, I'd like to be able to bring the particular db on the
> standby server online while I fix or restore what's on the primary
> server. Can it be done individually or is it an all or nothing
> scenario?

As far as I know there is no automatic failover with log shipping, but
you would have to failover clients manually.

If you are using database mirroring, failover can be automatic, and I
assume that this is per database, but I don't know.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Log Shipping Failover

Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
PepsThis is a multi-part message in MIME format.
--090006000402040507050100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Log shipping pairs are at the individual database level. As such you
can change the log shipping roles of the primary & secondary for a
single DB if you like without affecting any other databases that happen
to be shipping their logs to or from the primary or secondary servers
(with sp_change_secondary_role, sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the logshipping
maint plan, RESTORE...WITH RECOVERY on the secondary and create a
logshipping maint plan (on the secondary) in the other direction)).
For more info see BOL:
How to set up and perform a log shipping role change
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp
--
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:
>Is it possible to failover a single or a selected number of databases
>onto a standby server? We're currently log shipping about 12 databases
>to a standby and I was wondering if it was possible to randomly
>failover onto selected dbs.
>My understanding of log shipping is that when the primary server goes
>down, the standby takes over. But, does that mean that all of the db's
>on the standby server now become the primary? Or, can failover occur on
>selected dbs?
>
>The reason I ask is because if one db on the primary, for example, was
>to go down, I'd like to be able to bring the particular db on the
>standby server online while I fix or restore what's on the primary
>server. Can it be done individually or is it an all or nothing
>scenario?
>
>Your personal advice or links is greatly appreciated.
>
>Thanks,
>Peps
>
>
--090006000402040507050100
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Log shipping pairs are at the individual database level. As such
you can change the log shipping roles of the primary & secondary
for a single DB if you like without affecting any other databases that
happen to be shipping their logs to or from the primary or secondary
servers </tt><tt>(with sp_change_secondary_role,
sp_change_monitor_role &
sp_change_primary_role or with the SQLEM GUI (ie. delete the
logshipping maint plan, RESTORE...WITH RECOVERY on the secondary and
create a logshipping maint plan (on the secondary) in the other
direction)).<br>
<br>
For more info see BOL:<br>
<br>
How to set up and perform a log shipping role change<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
theSpinel wrote:
<blockquote
cite="mid1148526142.572880.159390@.g10g2000cwb.googlegroups.com"
type="cite">
<pre wrap="">Is it possible to failover a single or a selected number of databases
onto a standby server? We're currently log shipping about 12 databases
to a standby and I was wondering if it was possible to randomly
failover onto selected dbs.
My understanding of log shipping is that when the primary server goes
down, the standby takes over. But, does that mean that all of the db's
on the standby server now become the primary? Or, can failover occur on
selected dbs?
The reason I ask is because if one db on the primary, for example, was
to go down, I'd like to be able to bring the particular db on the
standby server online while I fix or restore what's on the primary
server. Can it be done individually or is it an all or nothing
scenario?
Your personal advice or links is greatly appreciated.
Thanks,
Peps
</pre>
</blockquote>
</body>
</html>
--090006000402040507050100--|||Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?|||This is a multi-part message in MIME format.
--000806010409090003060507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Those sp_change... procs are in the msdb database. You will need to
either switch to that database before running those procs (with the USE
statement) or explicitly state the full object name when executing it,
for example
exec msdb.dbo.sp_change_primary_role @.db_name='MyDB', ....;
Personally I would do this all with T-SQL statements, however it may be
easier for you to do it with the GUI:
1) manually start the SQLAgent "backup" job on the primary,
2) manually start the SQLAgent "copy" job on the primary,
3) delete the maint plan on the primary,
4) restore that log backup on the secondary DB using the WITH RECOVERY
option,
5) delete the SQLAgent "restore" job on the secondary;
6) then, optionally, you can establish the log shipping in the other
direction
just by going through the maint plan wizard on the secondary and
specifying
the old primary as the new secondary.
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same passwords
as on primary) and that they're not orphaned. Up to you.
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above):
How to set up and perform a log shipping role change (Transact-SQL)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp
Hope this helps.
--
*mike hodgson*
http://sqlnerd.blogspot.com
theSpinel wrote:
>Thanks, Mike.
>I tested this on one of the db's but I received an error about not
>finding the store procedure 'sp_change_primary_role.'
>Any ideas?
>
>
--000806010409090003060507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Those sp_change... procs are in the msdb database. You will need
to either switch to that database before running those procs (with the
USE statement) or explicitly state the full object name when executing
it, for example<br>
</tt>
<blockquote><tt>exec msdb.dbo.sp_change_primary_role @.db_name='MyDB',
....;</tt><br>
</blockquote>
<tt>Personally I would do this all with T-SQL statements, however it
may be easier for you to do it with the GUI:<br>
1) manually start the SQLAgent "backup" job on the primary,<br>
2) manually start the SQLAgent "copy" job on the primary,<br>
3) delete the maint plan on the primary,<br>
4) restore that log backup on the secondary DB using the WITH
RECOVERY option,<br>
5) delete the SQLAgent "restore" job on the secondary;<br>
6) then, optionally, you can establish the log shipping in the other
direction<br>
just by going through the maint plan wizard on the secondary and
specifying<br>
the old primary as the new secondary.<br>
And don't forget to make sure the logins work properly on the secondary
(I always forget that bit), ie. that they exist (with the same
passwords as on primary) and that they're not orphaned. Up to you.<br>
<br>
This BOL article is pretty concise in what you need to do (in
Transact-SQL) if you want to go the T-SQL way (just remember all the
sp_change... procs are in msdb (sp_resolve_logins is in master)) but
you'll need to go through the maint wizard afterwards to establish the
log shipping back in the other direction again (ie. step 6 above):<br>
<br>
How to set up and perform a log shipping role change (Transact-SQL)</tt><br>
<tt><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_1_013_9t0p.asp</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
theSpinel wrote:
<blockquote
cite="mid1148539314.052466.229210@.i39g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Thanks, Mike.
I tested this on one of the db's but I received an error about not
finding the store procedure 'sp_change_primary_role.'
Any ideas?
</pre>
</blockquote>
</body>
</html>
--000806010409090003060507--