Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only '
(Is that what the "No Recovery" mode is for?)
Thanks
JonoHi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only '
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono

Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only ?
(Is that what the "No Recovery" mode is for?)
Thanks
Jono
Hi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only ?
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono
sql

Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only '
(Is that what the "No Recovery" mode is for?)
Thanks
JonoHi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only '
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:
> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.
Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job faile
d
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is
terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence th
e
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>sql

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 error - database in use

I am using MS builtin log-shipping functionality in
SQL2000.
My log shipping maintenance plan has a secondary load
state of 'standby' mode. And I have selected
the 'terminate users in database' option - however,
occasionally my log-shipping job is failing with '3101 -
database in use' error.
However, I thought that by selecting 'terminate users in
database' option - this should never happen ?
Has anyone seen anything similar before ?'terminate users in database' will not work always and
does not guarantee anything, a terminated process may drop
into a rollback mode and then it could take a long time to
finally get disappeared.
>--Original Message--
>I am using MS builtin log-shipping functionality in
>SQL2000.
>My log shipping maintenance plan has a secondary load
>state of 'standby' mode. And I have selected
>the 'terminate users in database' option - however,
>occasionally my log-shipping job is failing with '3101 -
>database in use' error.
>However, I thought that by selecting 'terminate users in
>database' option - this should never happen ?
>Has anyone seen anything similar before ?
>.
>

log shipping enquires

Hi,
I have set up log shipping in the following :
load/copy frequency - 1 mins
backup frequency - 5 mins
secondary db - standby mode
issue :
when i made some changes to a record of the primary db's tbl , it was not
reflected in the secondary db's table.
Is it due to the fact that the secondary db is a Read-Only ?
and if i do want to delete log shipping , what shld be the correct way in
doing this
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1the reason why the cahnges in primary DB are not reflected on secondary
maybe that you didnt setup the log shipping properly.
You can cross check if if have any out of sync errors...
and abt deleting the log shipping the best way will be ... from the
maintenance plan remove log shipping and then delete the plan.|||Did you wait long enough for the changs to be sent? You can verify the tran
log backups by looking in the directories on each machine. You can also
verify the backup and restore by looking in the errorlog. The log shipping
monitor will display the status of everything. If it isn't getting there,
you will be seeing errors.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5af9a1affdb26@.uwe...
> Hi,
> I have set up log shipping in the following :
> load/copy frequency - 1 mins
> backup frequency - 5 mins
> secondary db - standby mode
> issue :
> when i made some changes to a record of the primary db's tbl , it was not
> reflected in the secondary db's table.
> Is it due to the fact that the secondary db is a Read-Only ?
> and if i do want to delete log shipping , what shld be the correct way in
> doing this
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||tks ..
Shadow wrote:
>the reason why the cahnges in primary DB are not reflected on secondary
>maybe that you didnt setup the log shipping properly.
>You can cross check if if have any out of sync errors...
>and abt deleting the log shipping the best way will be ... from the
>maintenance plan remove log shipping and then delete the plan.
Message posted via http://www.droptable.com|||Hi,
I have managed to set up the log shipping correctly as i could see the
changes on my destination DB . However, i have specified that copy is 0
minutes , and load is 1 minutes but it took something like around 5 minutes
to see the updated data in my destination DB.
how can i acutally make the updates "almost immediate" ? or this is normal
as copying the txn log takes time and need to wait till user "give up" the
exclusive lock , then copy the txn log over and then load from the txn log ?
appreciate ur advice
tks & rdgs
Michael Hotek wrote:[vbcol=seagreen]
>Did you wait long enough for the changs to be sent? You can verify the tra
n
>log backups by looking in the directories on each machine. You can also
>verify the backup and restore by looking in the errorlog. The log shipping
>monitor will display the status of everything. If it isn't getting there,
>you will be seeing errors.
>
>[quoted text clipped - 16 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1|||You can't. The entire thing runs as a set of jobs. The most frequently a
job can run is every minute and that is NOT recommended for log shipping
unless you want to watch the monitor throw a LOT of errors. You usually set
up the backup job at 2 minutes or more. (2 minutes is the lowest you want
to go). That means a new backup will be generated every 2 minutes. It then
takes time to copy over and time to restore. Most of that time is simply
the job shutting down and waiting for the SQL Server Agent to kick it off
again. 5 minutes start to finish is about the best you are going to get
with log shipping. If you need lower latency than that, you have to go to
either Database Mirroring (2005 only) or the replication engine.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5b378dc949992@.uwe...
> Hi,
> I have managed to set up the log shipping correctly as i could see the
> changes on my destination DB . However, i have specified that copy is 0
> minutes , and load is 1 minutes but it took something like around 5
> minutes
> to see the updated data in my destination DB.
> how can i acutally make the updates "almost immediate" ? or this is
> normal
> as copying the txn log takes time and need to wait till user "give up" the
> exclusive lock , then copy the txn log over and then load from the txn log
> ?
> appreciate ur advice
> tks & rdgs
>
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200602/1|||tks for ur clarifications ..
Cheers ..
Michael Hotek wrote:[vbcol=seagreen]
>You can't. The entire thing runs as a set of jobs. The most frequently a
>job can run is every minute and that is NOT recommended for log shipping
>unless you want to watch the monitor throw a LOT of errors. You usually se
t
>up the backup job at 2 minutes or more. (2 minutes is the lowest you want
>to go). That means a new backup will be generated every 2 minutes. It the
n
>takes time to copy over and time to restore. Most of that time is simply
>the job shutting down and waiting for the SQL Server Agent to kick it off
>again. 5 minutes start to finish is about the best you are going to get
>with log shipping. If you need lower latency than that, you have to go to
>either Database Mirroring (2005 only) or the replication engine.
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1

log shipping enquires

Hi,
I have set up log shipping in the following :
load/copy frequency - 1 mins
backup frequency - 5 mins
secondary db - standby mode
issue :
when i made some changes to a record of the primary db's tbl , it was not
reflected in the secondary db's table.
Is it due to the fact that the secondary db is a Read-Only ?
and if i do want to delete log shipping , what shld be the correct way in
doing this
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
the reason why the cahnges in primary DB are not reflected on secondary
maybe that you didnt setup the log shipping properly.
You can cross check if if have any out of sync errors...
and abt deleting the log shipping the best way will be ... from the
maintenance plan remove log shipping and then delete the plan.
|||Did you wait long enough for the changs to be sent? You can verify the tran
log backups by looking in the directories on each machine. You can also
verify the backup and restore by looking in the errorlog. The log shipping
monitor will display the status of everything. If it isn't getting there,
you will be seeing errors.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5af9a1affdb26@.uwe...
> Hi,
> I have set up log shipping in the following :
> load/copy frequency - 1 mins
> backup frequency - 5 mins
> secondary db - standby mode
> issue :
> when i made some changes to a record of the primary db's tbl , it was not
> reflected in the secondary db's table.
> Is it due to the fact that the secondary db is a Read-Only ?
> and if i do want to delete log shipping , what shld be the correct way in
> doing this
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||tks ..
Shadow wrote:
>the reason why the cahnges in primary DB are not reflected on secondary
>maybe that you didnt setup the log shipping properly.
>You can cross check if if have any out of sync errors...
>and abt deleting the log shipping the best way will be ... from the
>maintenance plan remove log shipping and then delete the plan.
Message posted via http://www.droptable.com
|||Hi,
I have managed to set up the log shipping correctly as i could see the
changes on my destination DB . However, i have specified that copy is 0
minutes , and load is 1 minutes but it took something like around 5 minutes
to see the updated data in my destination DB.
how can i acutally make the updates "almost immediate" ? or this is normal
as copying the txn log takes time and need to wait till user "give up" the
exclusive lock , then copy the txn log over and then load from the txn log ?
appreciate ur advice
tks & rdgs
Michael Hotek wrote:[vbcol=seagreen]
>Did you wait long enough for the changs to be sent? You can verify the tran
>log backups by looking in the directories on each machine. You can also
>verify the backup and restore by looking in the errorlog. The log shipping
>monitor will display the status of everything. If it isn't getting there,
>you will be seeing errors.
>[quoted text clipped - 16 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
|||You can't. The entire thing runs as a set of jobs. The most frequently a
job can run is every minute and that is NOT recommended for log shipping
unless you want to watch the monitor throw a LOT of errors. You usually set
up the backup job at 2 minutes or more. (2 minutes is the lowest you want
to go). That means a new backup will be generated every 2 minutes. It then
takes time to copy over and time to restore. Most of that time is simply
the job shutting down and waiting for the SQL Server Agent to kick it off
again. 5 minutes start to finish is about the best you are going to get
with log shipping. If you need lower latency than that, you have to go to
either Database Mirroring (2005 only) or the replication engine.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5b378dc949992@.uwe...
> Hi,
> I have managed to set up the log shipping correctly as i could see the
> changes on my destination DB . However, i have specified that copy is 0
> minutes , and load is 1 minutes but it took something like around 5
> minutes
> to see the updated data in my destination DB.
> how can i acutally make the updates "almost immediate" ? or this is
> normal
> as copying the txn log takes time and need to wait till user "give up" the
> exclusive lock , then copy the txn log over and then load from the txn log
> ?
> appreciate ur advice
> tks & rdgs
>
> Michael Hotek wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
|||tks for ur clarifications ..
Cheers ..
Michael Hotek wrote:[vbcol=seagreen]
>You can't. The entire thing runs as a set of jobs. The most frequently a
>job can run is every minute and that is NOT recommended for log shipping
>unless you want to watch the monitor throw a LOT of errors. You usually set
>up the backup job at 2 minutes or more. (2 minutes is the lowest you want
>to go). That means a new backup will be generated every 2 minutes. It then
>takes time to copy over and time to restore. Most of that time is simply
>the job shutting down and waiting for the SQL Server Agent to kick it off
>again. 5 minutes start to finish is about the best you are going to get
>with log shipping. If you need lower latency than that, you have to go to
>either Database Mirroring (2005 only) or the replication engine.
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1

log shipping enquires

Hi,
I have set up log shipping in the following :
load/copy frequency - 1 mins
backup frequency - 5 mins
secondary db - standby mode
issue :
when i made some changes to a record of the primary db's tbl , it was not
reflected in the secondary db's table.
Is it due to the fact that the secondary db is a Read-Only ?
and if i do want to delete log shipping , what shld be the correct way in
doing this
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1the reason why the cahnges in primary DB are not reflected on secondary
maybe that you didnt setup the log shipping properly.
You can cross check if if have any out of sync errors...
and abt deleting the log shipping the best way will be ... from the
maintenance plan remove log shipping and then delete the plan.|||Did you wait long enough for the changs to be sent? You can verify the tran
log backups by looking in the directories on each machine. You can also
verify the backup and restore by looking in the errorlog. The log shipping
monitor will display the status of everything. If it isn't getting there,
you will be seeing errors.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:5af9a1affdb26@.uwe...
> Hi,
> I have set up log shipping in the following :
> load/copy frequency - 1 mins
> backup frequency - 5 mins
> secondary db - standby mode
> issue :
> when i made some changes to a record of the primary db's tbl , it was not
> reflected in the secondary db's table.
> Is it due to the fact that the secondary db is a Read-Only ?
> and if i do want to delete log shipping , what shld be the correct way in
> doing this
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||tks ..
Shadow wrote:
>the reason why the cahnges in primary DB are not reflected on secondary
>maybe that you didnt setup the log shipping properly.
>You can cross check if if have any out of sync errors...
>and abt deleting the log shipping the best way will be ... from the
>maintenance plan remove log shipping and then delete the plan.
--
Message posted via http://www.sqlmonster.com|||Hi,
I have managed to set up the log shipping correctly as i could see the
changes on my destination DB . However, i have specified that copy is 0
minutes , and load is 1 minutes but it took something like around 5 minutes
to see the updated data in my destination DB.
how can i acutally make the updates "almost immediate" ? or this is normal
as copying the txn log takes time and need to wait till user "give up" the
exclusive lock , then copy the txn log over and then load from the txn log ?
appreciate ur advice
tks & rdgs
Michael Hotek wrote:
>Did you wait long enough for the changs to be sent? You can verify the tran
>log backups by looking in the directories on each machine. You can also
>verify the backup and restore by looking in the errorlog. The log shipping
>monitor will display the status of everything. If it isn't getting there,
>you will be seeing errors.
>> Hi,
>[quoted text clipped - 16 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||You can't. The entire thing runs as a set of jobs. The most frequently a
job can run is every minute and that is NOT recommended for log shipping
unless you want to watch the monitor throw a LOT of errors. You usually set
up the backup job at 2 minutes or more. (2 minutes is the lowest you want
to go). That means a new backup will be generated every 2 minutes. It then
takes time to copy over and time to restore. Most of that time is simply
the job shutting down and waiting for the SQL Server Agent to kick it off
again. 5 minutes start to finish is about the best you are going to get
with log shipping. If you need lower latency than that, you have to go to
either Database Mirroring (2005 only) or the replication engine.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:5b378dc949992@.uwe...
> Hi,
> I have managed to set up the log shipping correctly as i could see the
> changes on my destination DB . However, i have specified that copy is 0
> minutes , and load is 1 minutes but it took something like around 5
> minutes
> to see the updated data in my destination DB.
> how can i acutally make the updates "almost immediate" ? or this is
> normal
> as copying the txn log takes time and need to wait till user "give up" the
> exclusive lock , then copy the txn log over and then load from the txn log
> ?
> appreciate ur advice
> tks & rdgs
>
> Michael Hotek wrote:
>>Did you wait long enough for the changs to be sent? You can verify the
>>tran
>>log backups by looking in the directories on each machine. You can also
>>verify the backup and restore by looking in the errorlog. The log
>>shipping
>>monitor will display the status of everything. If it isn't getting there,
>>you will be seeing errors.
>> Hi,
>>[quoted text clipped - 16 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||tks for ur clarifications ..
Cheers ..
Michael Hotek wrote:
>You can't. The entire thing runs as a set of jobs. The most frequently a
>job can run is every minute and that is NOT recommended for log shipping
>unless you want to watch the monitor throw a LOT of errors. You usually set
>up the backup job at 2 minutes or more. (2 minutes is the lowest you want
>to go). That means a new backup will be generated every 2 minutes. It then
>takes time to copy over and time to restore. Most of that time is simply
>the job shutting down and waiting for the SQL Server Agent to kick it off
>again. 5 minutes start to finish is about the best you are going to get
>with log shipping. If you need lower latency than that, you have to go to
>either Database Mirroring (2005 only) or the replication engine.
>> Hi,
>[quoted text clipped - 26 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1

Log shipping Copy and Load frequency

Whats the best interval needed to set up the difference between the copy and
load frequency for log shipping. What i mean is if i set up the copy and
load each for 10 mins, would there be ever a contention where the load is
trying to restore a log file being copied also invoked at the same time
resulting in error " File being used by another process" since I have seen
those errors on our failure of restore jobs.
ThanksIt depends on the amount of transactions performed between the log =shipping routines and the performance of your servers. We perform log =shipping every 30 minutes. The restore the the standby server takes =less than 30 seconds.
-- Keith, SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message =news:u0kTuxcSDHA.1552@.TK2MSFTNGP10.phx.gbl...
> Whats the best interval needed to set up the difference between the =copy and
> load frequency for log shipping. What i mean is if i set up the copy =and
> load each for 10 mins, would there be ever a contention where the load =is
> trying to restore a log file being copied also invoked at the same =time
> resulting in error " File being used by another process" since I have =seen
> those errors on our failure of restore jobs.
> > Thanks
> > >