Log shipping trn file retention

What's the logshipping trn file retention most DBA usually set, 1,2 or 3
days? I am curious. In our production database, we set our LS file retention
for 3 days and I am just wonder if it 's too long or too short. Is there a
guideline or some short.
Thanks in advance
PhilN wrote:
> What's the logshipping trn file retention most DBA usually set, 1,2 or 3
> days? I am curious. In our production database, we set our LS file retention
> for 3 days and I am just wonder if it 's too long or too short. Is there a
> guideline or some short.
> Thanks in advance
>
We use a home-grown log shipping process here, and keep a solid week's
worth of backups. As each log is restored, it is zipped up into an
archive for that day. At any given point, we have 7 zip files,
containing the t-logs for the previous 7 days.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log shipping trn file retention

What's the logshipping trn file retention most DBA usually set, 1,2 or 3
days? I am curious. In our production database, we set our LS file retention
for 3 days and I am just wonder if it 's too long or too short. Is there a
guideline or some short.
Thanks in advancePhilN wrote:
> What's the logshipping trn file retention most DBA usually set, 1,2 or 3
> days? I am curious. In our production database, we set our LS file retention
> for 3 days and I am just wonder if it 's too long or too short. Is there a
> guideline or some short.
> Thanks in advance
>
We use a home-grown log shipping process here, and keep a solid week's
worth of backups. As each log is restored, it is zipped up into an
archive for that day. At any given point, we have 7 zip files,
containing the t-logs for the previous 7 days.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Log shipping trn file retention

What's the logshipping trn file retention most DBA usually set, 1,2 or 3
days? I am curious. In our production database, we set our LS file retention
for 3 days and I am just wonder if it 's too long or too short. Is there a
guideline or some short.
Thanks in advancePhilN wrote:
> What's the logshipping trn file retention most DBA usually set, 1,2 or 3
> days? I am curious. In our production database, we set our LS file retenti
on
> for 3 days and I am just wonder if it 's too long or too short. Is there a
> guideline or some short.
> Thanks in advance
>
We use a home-grown log shipping process here, and keep a solid week's
worth of backups. As each log is restored, it is zipped up into an
archive for that day. At any given point, we have 7 zip files,
containing the t-logs for the previous 7 days.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log shipping TLog dump naming covention

The Tlog dump as part of Log shipping has got me wondering on how the time
portion of the file name is done. So right now I have a file name called
DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
looking at the file, how can you tell the time ? I am using SQL 2005. It was
better in SQL 2000.
Thanks
Pls refer
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1773170&SiteID=1
it uses UTC timing convention I believe !
Thanxx
Deepak
"Hassan" wrote:

> The Tlog dump as part of Log shipping has got me wondering on how the time
> portion of the file name is done. So right now I have a file name called
> DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
> looking at the file, how can you tell the time ? I am using SQL 2005. It was
> better in SQL 2000.
> Thanks
>
>
>

Log shipping TLog dump naming covention

The Tlog dump as part of Log shipping has got me wondering on how the time
portion of the file name is done. So right now I have a file name called
DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
looking at the file, how can you tell the time ? I am using SQL 2005. It was
better in SQL 2000.
ThanksPls refer
http://forums.microsoft.com/MSDN/Sh...773170&SiteID=1
it uses UTC timing convention I believe !
--
Thanxx
Deepak
"Hassan" wrote:

> The Tlog dump as part of Log shipping has got me wondering on how the time
> portion of the file name is done. So right now I have a file name called
> DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
> looking at the file, how can you tell the time ? I am using SQL 2005. It w
as
> better in SQL 2000.
> Thanks
>
>
>

Log shipping TLog dump naming covention

The Tlog dump as part of Log shipping has got me wondering on how the time
portion of the file name is done. So right now I have a file name called
DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
looking at the file, how can you tell the time ? I am using SQL 2005. It was
better in SQL 2000.
ThanksPls refer
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1773170&SiteID=1
it uses UTC timing convention I believe !
--
Thanxx
Deepak
"Hassan" wrote:
> The Tlog dump as part of Log shipping has got me wondering on how the time
> portion of the file name is done. So right now I have a file name called
> DBName_20071109204000.trn that was taken at 12:40pm on 11/9/2007. Just
> looking at the file, how can you tell the time ? I am using SQL 2005. It was
> better in SQL 2000.
> Thanks
>
>
>

Log Shipping Question

Hi Everyone,
The logs that are copied to the standby server, what is the file that has a
..tuf extension?
Thanks in advance
Larry
Its the undo file when you setup log shipping in standby mode.. Look at undo
file in the restore database section in BOL
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:E4DD40E8-593D-4787-BA5F-F2DC8B25CA64@.microsoft.com...
> Hi Everyone,
> The logs that are copied to the standby server, what is the file that has
a
> .tuf extension?
> Thanks in advance
> Larry

Log Shipping Question

Hi Everyone,
The logs that are copied to the standby server, what is the file that has a
.tuf extension?
Thanks in advance
LarryIts the undo file when you setup log shipping in standby mode.. Look at undo
file in the restore database section in BOL
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:E4DD40E8-593D-4787-BA5F-F2DC8B25CA64@.microsoft.com...
> Hi Everyone,
> The logs that are copied to the standby server, what is the file that has
a
> .tuf extension?
> Thanks in advance
> Larry

log shipping problem

I have 2 server 1 primary 2nd secondary
I have configured log shipping on them'
Now everything works fine, no errors any where bt file is not copied in the
secondary server folder.
any if I run the copy job on secondary server it gives following output
output
----
----
----
--
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'DB_OLD'
as 'DB_OLD\sqlservice' (trusted)
NULL
NULL
Starting copy for plan 10.11.11.11.CCM_logshipping
NULL
Source database - CCM
Copied 0 files
NULL
Finished copy for plan 10.11.11.11.CCM_logshipping
I have configure a/c on both server, a/c are local account under which sql
agent and server runs
Please let me know what can be the problem.Hi,
I think this is mainly access permisssion issue.
May user of primary server don't have access to write in secondary
server.
Regards,
Sajid C.
VSS wrote:
> I have 2 server 1 primary 2nd secondary
> I have configured log shipping on them'
> Now everything works fine, no errors any where bt file is not copied in th
e
> secondary server folder.
> any if I run the copy job on secondary server it gives following output
> output
> ----
--
> ----
--
> ----
--
> --
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> Logged on to SQL Server 'DB_OLD'
> as 'DB_OLD\sqlservice' (trusted)
> NULL
> NULL
> Starting copy for plan 10.11.11.11.CCM_logshipping
> NULL
> Source database - CCM
> Copied 0 files
> NULL
> Finished copy for plan 10.11.11.11.CCM_logshipping
> I have configure a/c on both server, a/c are local account under which sql
> agent and server runs
> Please let me know what can be the problem.

log shipping problem

I have 2 server 1 primary 2nd secondary
I have configured log shipping on them'
Now everything works fine, no errors any where bt file is not copied in the
secondary server folder.
any if I run the copy job on secondary server it gives following output
output
----
----
----
--
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'DB_OLD'
as 'DB_OLD\sqlservice' (trusted)
NULL
NULL
Starting copy for plan 10.11.11.11.CCM_logshipping
NULL
Source database - CCM
Copied 0 files
NULL
Finished copy for plan 10.11.11.11.CCM_logshipping
I have configure a/c on both server, a/c are local account under which sql
agent and server runs
Please let me know what can be the problem.Hi,
I think this is mainly access permisssion issue.
May user of primary server don't have access to write in secondary
server.
Regards,
Sajid C.
VSS wrote:
> I have 2 server 1 primary 2nd secondary
> I have configured log shipping on them'
> Now everything works fine, no errors any where bt file is not copied in the
> secondary server folder.
> any if I run the copy job on secondary server it gives following output
> output
> ----
> ----
> ----
> --
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> Logged on to SQL Server 'DB_OLD'
> as 'DB_OLD\sqlservice' (trusted)
> NULL
> NULL
> Starting copy for plan 10.11.11.11.CCM_logshipping
> NULL
> Source database - CCM
> Copied 0 files
> NULL
> Finished copy for plan 10.11.11.11.CCM_logshipping
> I have configure a/c on both server, a/c are local account under which sql
> agent and server runs
> Please let me know what can be the problem.

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 not Loading TRN file

I cannot get Log Shipping to work. It does not give any outward
errors. Everything seams to work correctly except the secondary server
is not picking up the Transaction logs and applying them. The TRN
files are being written to a shared directory on the primary server as
scheduled.
I tracked down the following error on the scecondary server from the
Log Shipping copy job.
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'DATABASE2\Dev'
as 'NT AUTHORITY\SYSTEM' (trusted)
[Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with
a trusted SQL Server connection.
Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
Source database - CMX_Dev
Copied 0 files
Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
I have two Enterprise SQL Server 2000 machines (Database1 &
Database2). They are not part of any domain. They both are running
SQL Server and Agent under the identical user which has Administration
priviledges. Both machines can see the shared drive and have full
access. The shared drive is on Database1 and Database1 is also
currently the monitoring server.
Any suggestions would be appreciated...
Thanks
Calvin,
I have a related problem (the destination server is in a disaster site
without any domain)
It seems the Windows account who creates and runs the logshipping has
sa access on the destination server
The error suggest you were able to create a login on the destination
server for the user SYSTEM on local domain (=machine) NT AUTHORITY
(this is what a plan to do, but since I cannot browse the domain of
the primary server I plan to fiddle with the host file
Did you assign administrator role to this login ?
HTH
Jan Waumans
On 6 Apr 2004 13:46:10 -0700, CalvinNSlater@.Hotmail.com (Calvin
Slater) wrote:

>I cannot get Log Shipping to work. It does not give any outward
>errors. Everything seams to work correctly except the secondary server
>is not picking up the Transaction logs and applying them. The TRN
>files are being written to a shared directory on the primary server as
>scheduled.
> I tracked down the following error on the scecondary server from the
>Log Shipping copy job.
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> Logged on to SQL Server 'DATABASE2\Dev'
> as 'NT AUTHORITY\SYSTEM' (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user '(null)'. Reason: Not associated with
> a trusted SQL Server connection.
> Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
> Source database - CMX_Dev
> Copied 0 files
> Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
>
>I have two Enterprise SQL Server 2000 machines (Database1 &
>Database2). They are not part of any domain. They both are running
>SQL Server and Agent under the identical user which has Administration
>priviledges. Both machines can see the shared drive and have full
>access. The shared drive is on Database1 and Database1 is also
>currently the monitoring server.
>Any suggestions would be appreciated...
>Thanks

Log Shipping not Loading TRN file

I cannot get Log Shipping to work. It does not give any outward
errors. Everything seams to work correctly except the secondary server
is not picking up the Transaction logs and applying them. The TRN
files are being written to a shared directory on the primary server as
scheduled.
I tracked down the following error on the scecondary server from the
Log Shipping copy job.
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'DATABASE2\Dev'
as 'NT AUTHORITY\SYSTEM' (trusted)
[Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with
a trusted SQL Server connection.
Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
Source database - CMX_Dev
Copied 0 files
Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
I have two Enterprise SQL Server 2000 machines (Database1 &
Database2). They are not part of any domain. They both are running
SQL Server and Agent under the identical user which has Administration
priviledges. Both machines can see the shared drive and have full
access. The shared drive is on Database1 and Database1 is also
currently the monitoring server.
Any suggestions would be appreciated...
ThanksCalvin,
I have a related problem (the destination server is in a disaster site
without any domain)
It seems the Windows account who creates and runs the logshipping has
sa access on the destination server
The error suggest you were able to create a login on the destination
server for the user SYSTEM on local domain (=machine) NT AUTHORITY
(this is what a plan to do, but since I cannot browse the domain of
the primary server I plan to fiddle with the host file
Did you assign administrator role to this login ?
HTH
Jan Waumans
On 6 Apr 2004 13:46:10 -0700, CalvinNSlater@.Hotmail.com (Calvin
Slater) wrote:

>I cannot get Log Shipping to work. It does not give any outward
>errors. Everything seams to work correctly except the secondary server
>is not picking up the Transaction logs and applying them. The TRN
>files are being written to a shared directory on the primary server as
>scheduled.
> I tracked down the following error on the scecondary server from the
>Log Shipping copy job.
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> Logged on to SQL Server 'DATABASE2\Dev'
> as 'NT AUTHORITY\SYSTEM' (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user '(null)'. Reason: Not associated with
> a trusted SQL Server connection.
> Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
> Source database - CMX_Dev
> Copied 0 files
> Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
>
>I have two Enterprise SQL Server 2000 machines (Database1 &
>Database2). They are not part of any domain. They both are running
>SQL Server and Agent under the identical user which has Administration
>priviledges. Both machines can see the shared drive and have full
>access. The shared drive is on Database1 and Database1 is also
>currently the monitoring server.
>Any suggestions would be appreciated...
>Thanks

Log Shipping not Loading TRN file

I cannot get Log Shipping to work. It does not give any outward
errors. Everything seams to work correctly except the secondary server
is not picking up the Transaction logs and applying them. The TRN
files are being written to a shared directory on the primary server as
scheduled.
I tracked down the following error on the scecondary server from the
Log Shipping copy job.
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
Logged on to SQL Server 'DATABASE2\Dev'
as 'NT AUTHORITY\SYSTEM' (trusted)
[Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with
a trusted SQL Server connection.
Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
Source database - CMX_Dev
Copied 0 files
Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
I have two Enterprise SQL Server 2000 machines (Database1 &
Database2). They are not part of any domain. They both are running
SQL Server and Agent under the identical user which has Administration
priviledges. Both machines can see the shared drive and have full
access. The shared drive is on Database1 and Database1 is also
currently the monitoring server.
Any suggestions would be appreciated...
ThanksCalvin,
I have a related problem (the destination server is in a disaster site
without any domain)
It seems the Windows account who creates and runs the logshipping has
sa access on the destination server
The error suggest you were able to create a login on the destination
server for the user SYSTEM on local domain (=machine) NT AUTHORITY
(this is what a plan to do, but since I cannot browse the domain of
the primary server I plan to fiddle with the host file
Did you assign administrator role to this login ?
HTH
Jan Waumans
On 6 Apr 2004 13:46:10 -0700, CalvinNSlater@.Hotmail.com (Calvin
Slater) wrote:
>I cannot get Log Shipping to work. It does not give any outward
>errors. Everything seams to work correctly except the secondary server
>is not picking up the Transaction logs and applying them. The TRN
>files are being written to a shared directory on the primary server as
>scheduled.
> I tracked down the following error on the scecondary server from the
>Log Shipping copy job.
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> Logged on to SQL Server 'DATABASE2\Dev'
> as 'NT AUTHORITY\SYSTEM' (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18452:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user '(null)'. Reason: Not associated with
> a trusted SQL Server connection.
> Starting copy for plan DATABASE1\Dev.CMX_Dev_logshipping
> Source database - CMX_Dev
> Copied 0 files
> Finished copy for plan DATABASE1\Dev.CMX_Dev_logshipping
>
>I have two Enterprise SQL Server 2000 machines (Database1 &
>Database2). They are not part of any domain. They both are running
>SQL Server and Agent under the identical user which has Administration
>priviledges. Both machines can see the shared drive and have full
>access. The shared drive is on Database1 and Database1 is also
>currently the monitoring server.
>Any suggestions would be appreciated...
>Thanks

Log Shipping In Syn?

Hi,
If I set up the backup of the log file every 5 mins and I also setup the
copy every 5 mins same as restore every 5 mins, I don't think I am going to
get the database updated every 5 mins since the backup and copy could take
more time to finish. Is there any way to make sure everything in sequence,
Cackup-->Copy-->Restore in stead of calculating the time for log shipping?
Thanks
Ed
Ed - one thing you can do is to have a higher frequency fro the restores
than the copies eg once a minute for the restore. This way you can minimise
the dead time.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||That is a good idea, Paul. I think the same setup can be applied for copy
and backup, higher frequency for copy than backup
Thanks
"Paul Ibison" wrote:

> Ed - one thing you can do is to have a higher frequency fro the restores
> than the copies eg once a minute for the restore. This way you can minimise
> the dead time.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There are two ways of doing log shipping - synchronously - the way you do
it, and asynchronously.
With synchronous a job, does the backup, copies it to the secondary, and
then applies the log there. Then the job completes and the next scheduled
job runs. If there is some overlap you miss a scheduled backup, copy,
restore operation, but log shipping is resilient to this.
With asynchronous you dump, and then another job does the copy and a third
job does the restore. Backups will be queued at the primary until they are
copied, and then queued at the secondary and applied in order there. The
advantage of this is that your exposure to data loss is much less.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:9F855BA3-1813-4ACA-B2A0-008FC8ED8B40@.microsoft.com...
> Hi,
> If I set up the backup of the log file every 5 mins and I also setup the
> copy every 5 mins same as restore every 5 mins, I don't think I am going
> to
> get the database updated every 5 mins since the backup and copy could take
> more time to finish. Is there any way to make sure everything in
> sequence,
> Cackup-->Copy-->Restore in stead of calculating the time for log shipping?
> Thanks
> Ed
|||Hilary ,
I would like to make sure I don't misunderstand what you mean
Do you mean I should put three steps (backup --> copy --> restore) into one
job? Inside the job, I should do "Go To Next Step if succeed" in order to do
"synchronously".
The way that the SSMS uses to setup is "asynchronously"?
Thanks again
Ed
"Hilary Cotter" wrote:

> There are two ways of doing log shipping - synchronously - the way you do
> it, and asynchronously.
> With synchronous a job, does the backup, copies it to the secondary, and
> then applies the log there. Then the job completes and the next scheduled
> job runs. If there is some overlap you miss a scheduled backup, copy,
> restore operation, but log shipping is resilient to this.
> With asynchronous you dump, and then another job does the copy and a third
> job does the restore. Backups will be queued at the primary until they are
> copied, and then queued at the secondary and applied in order there. The
> advantage of this is that your exposure to data loss is much less.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:9F855BA3-1813-4ACA-B2A0-008FC8ED8B40@.microsoft.com...
>
>
|||Yes, for an example of how to do this have a look at this -
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:D29A7CCA-621C-4202-9452-AE814179F8D4@.microsoft.com...[vbcol=seagreen]
> Hilary ,
> I would like to make sure I don't misunderstand what you mean
> Do you mean I should put three steps (backup --> copy --> restore) into
> one
> job? Inside the job, I should do "Go To Next Step if succeed" in order to
> do
> "synchronously".
> The way that the SSMS uses to setup is "asynchronously"?
> Thanks again
> Ed
> "Hilary Cotter" wrote:

Log Shipping file deletes

I have log shipping working on my OLTP server. One and
only one of the log shipped databases is not deleting the
transaction log files on the destination server.
The deletes are happening on the source server, and they
are happening for all other databases on the destination
server.
I have looked at the various log shipping tables in msdb,
and I can't find any differences to explain this.
Has anyone got any ideas ?
TIA.Take a look also a file permissions and ownership... Is there a difference
there?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"im Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:3bc001c37b2e$494b75a0$a601280a@.phx.gbl...
> I have log shipping working on my OLTP server. One and
> only one of the log shipped databases is not deleting the
> transaction log files on the destination server.
> The deletes are happening on the source server, and they
> are happening for all other databases on the destination
> server.
> I have looked at the various log shipping tables in msdb,
> and I can't find any differences to explain this.
> Has anyone got any ideas ?
> TIA.

Log shipping error

I m getting following error in copy/restore operation
"The process cannot access the file because it is being used by another
process."
This come against the file first_file_000000000000.trn every time copy
restore is done. Only first time it went successful. Error number is 32.
Please tell me how to avoid thisVSS
You could be getting this messaqge because it is trying to restore the log
before the copy is finished. How often do you backup and copy the transaction
log?
There is a setting for load delay. The default is 0, you could try setting
that to wait longer for the copy to finish.
Hope this helps
John
"VSS" wrote:
> I m getting following error in copy/restore operation
> "The process cannot access the file because it is being used by another
> process."
> This come against the file first_file_000000000000.trn every time copy
> restore is done. Only first time it went successful. Error number is 32.
> Please tell me how to avoid this
>
>|||VSS wrote:
> I m getting following error in copy/restore operation
> "The process cannot access the file because it is being used by another
> process."
> This come against the file first_file_000000000000.trn every time copy
> restore is done. Only first time it went successful. Error number is 32.
> Please tell me how to avoid this
>
Isn't it obvious what you need to do? The error message tells you that
some other process is using the file that you're trying to copy/restore.
Not a SQL Server problem. You need to figure out what is using that
file. Take a look at http://www.sysinternals.com for some utilities
that will help you figure that out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log shipping error

I m getting following error in copy/restore operation
"The process cannot access the file because it is being used by another
process."
This come against the file first_file_000000000000.trn every time copy
restore is done. Only first time it went successful. Error number is 32.
Please tell me how to avoid thisVSS
You could be getting this messaqge because it is trying to restore the log
before the copy is finished. How often do you backup and copy the transactio
n
log?
There is a setting for load delay. The default is 0, you could try setting
that to wait longer for the copy to finish.
Hope this helps
John
"VSS" wrote:

> I m getting following error in copy/restore operation
> "The process cannot access the file because it is being used by another
> process."
> This come against the file first_file_000000000000.trn every time copy
> restore is done. Only first time it went successful. Error number is 32.
> Please tell me how to avoid this
>
>|||VSS wrote:
> I m getting following error in copy/restore operation
> "The process cannot access the file because it is being used by another
> process."
> This come against the file first_file_000000000000.trn every time copy
> restore is done. Only first time it went successful. Error number is 32.
> Please tell me how to avoid this
>
Isn't it obvious what you need to do? The error message tells you that
some other process is using the file that you're trying to copy/restore.
Not a SQL Server problem. You need to figure out what is using that
file. Take a look at http://www.sysinternals.com for some utilities
that will help you figure that out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com