Log shipping goes out of sync

Hi
I have two SQL 2000 boxes setup to log ship. The box being shipped to
is also the monitor
The size of the db being shipped is around 110GB - so the initial log
ship first creates and copies across the entire db (takes a while)
then begins the transaction logs.
The problem is that the log ship goes out of sync also immediately -
from testing I've managed to get the first shipped transaction log to
load, and sometimes the second, but never further as it gets out of
sync.
Things I've tried:
Changing the schedule from the default 15 minutes to 2 hours (for copy
and load)
Ensuring the log ship process doesn't clash with a routine backup
Any suggestions?
Thanks
TobyOn Feb 19, 7:40 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> I have two SQL 2000 boxes setup to log ship. The box being shipped to
> is also the monitor
> The size of the db being shipped is around 110GB - so the initial log
> ship first creates and copies across the entire db (takes a while)
> then begins the transaction logs.
> The problem is that the log ship goes out of sync also immediately -
> from testing I've managed to get the first shipped transaction log to
> load, and sometimes the second, but never further as it gets out of
> sync.
> Things I've tried:
> Changing the schedule from the default 15 minutes to 2 hours (for copy
> and load)
> Ensuring the log ship process doesn't clash with a routine backup
> Any suggestions?
> Thanks
> Toby
Please explain this further:
"Ensuring the log ship process doesn't clash with a routine backup"
Are you running transaction log backups IN ADDITION to the log
shipping process? This will break the log shipping chain. Log
shipping works by taking a backup of the transaction log and restoring
that backup onto another database. If you run your own independent
log backup, you're advancing the LSN pointer, throwing the log
shipping backups out of sync.|||Hi
In reply to your question "Are you running transaction log backups IN
ADDITION to the log
> shipping process? " - the answer is yes I am, so clearly here lies the problem.
I'm running a daily log backup, truncate and shrink - which I realise
now is going to cause issues with the log shipping. However, and
forgive me if this appears trivial, but the reason for run a log
backup, truncate and shrink is to prevent the log file getting too
big, as it currently grows at the rate of 20-30gb a day. If I rely on
the log shipping process only, will this provide adequate truncating
of the log?
Thanks|||On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> In reply to your question "Are you running transaction log backups IN
> ADDITION to the log
> > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> I'm running a daily log backup, truncate and shrink - which I realise
> now is going to cause issues with the log shipping. However, and
> forgive me if this appears trivial, but the reason for run a log
> backup, truncate and shrink is to prevent the log file getting too
> big, as it currently grows at the rate of 20-30gb a day. If I rely on
> the log shipping process only, will this provide adequate truncating
> of the log?
> Thanks
Couple of key things here:
1. Log backups truncate the log - the more frequently that you run a
log backup, the quicker committed transactions will get truncated, and
the less likely your log is to grow. Note that LARGE transactions can
still cause growth, because they can't be truncated until fully
committed.
2. You are hurting your overall performance in one, possibly two,
ways. By repeatedly shrinking the log file, you are forcing SQL
Server to grow it again as needed, which introduces additional
overhead, possibly during a busy period. Also, repeatedly growing/
shrinking/growing/shrinking will lead to disk fragmentation, which
will also ultimately hurt your performance.
My advice would be to not use the log-shipping wizard that is built-
in. You can kill two birds with one stone by writing your own backup
routines. Create a log backup job that runs every hour (we do 5-
minute intervals here), have that job create backup files that contain
a date/time stamp, place these files into some folder, let's say
"FolderX". Write a log-shipping routine that monitors FolderX for new
files. When a new file is detected, your log shipping routine should
restore it and record the file name in a logging table. It then goes
back to monitoring FolderX for new files that aren't in the logging
table.
It's really not as complicated as it seems, and you'll solve all of
these problems...|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
> > Hi
> > In reply to your question "Are you running transaction log backups IN
> > ADDITION to the log
> > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > I'm running a daily log backup, truncate and shrink - which I realise
> > now is going to cause issues with the log shipping. However, and
> > forgive me if this appears trivial, but the reason for run a log
> > backup, truncate and shrink is to prevent the log file getting too
> > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > the log shipping process only, will this provide adequate truncating
> > of the log?
> > Thanks
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
OK that's great thanks. I really don't know why I had the task shrink
the log in the first place, given the rate it increases.|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
> > Hi
> > In reply to your question "Are you running transaction log backups IN
> > ADDITION to the log
> > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > I'm running a daily log backup, truncate and shrink - which I realise
> > now is going to cause issues with the log shipping. However, and
> > forgive me if this appears trivial, but the reason for run a log
> > backup, truncate and shrink is to prevent the log file getting too
> > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > the log shipping process only, will this provide adequate truncating
> > of the log?
> > Thanks
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
Hi again
I now have the transaction log shipping running fine - I have used the
Wizard for now so I'll see how it goes.
One thing though - having removed the additional process to backup and
truncate the log, the log is now not being truncated.
Any thoughts?
Thanks.|||On Feb 25, 6:44 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
>
> > On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
> > > Hi
> > > In reply to your question "Are you running transaction log backups IN
> > > ADDITION to the log
> > > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > > I'm running a daily log backup, truncate and shrink - which I realise
> > > now is going to cause issues with the log shipping. However, and
> > > forgive me if this appears trivial, but the reason for run a log
> > > backup, truncate and shrink is to prevent the log file getting too
> > > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > > the log shipping process only, will this provide adequate truncating
> > > of the log?
> > > Thanks
> > Couple of key things here:
> > 1. Log backups truncate the log - the more frequently that you run a
> > log backup, the quicker committed transactions will get truncated, and
> > the less likely your log is to grow. Note that LARGE transactions can
> > still cause growth, because they can't be truncated until fully
> > committed.
> > 2. You are hurting your overall performance in one, possibly two,
> > ways. By repeatedly shrinking the log file, you are forcing SQL
> > Server to grow it again as needed, which introduces additional
> > overhead, possibly during a busy period. Also, repeatedly growing/
> > shrinking/growing/shrinking will lead to disk fragmentation, which
> > will also ultimately hurt your performance.
> > My advice would be to not use the log-shipping wizard that is built-
> > in. You can kill two birds with one stone by writing your own backup
> > routines. Create a log backup job that runs every hour (we do 5-
> > minute intervals here), have that job create backup files that contain
> > a date/time stamp, place these files into some folder, let's say
> > "FolderX". Write a log-shipping routine that monitors FolderX for new
> > files. When a new file is detected, your log shipping routine should
> > restore it and record the file name in a logging table. It then goes
> > back to monitoring FolderX for new files that aren't in the logging
> > table.
> > It's really not as complicated as it seems, and you'll solve all of
> > these problems...
> Hi again
> I now have the transaction log shipping running fine - I have used the
> Wizard for now so I'll see how it goes.
> One thing though - having removed the additional process to backup and
> truncate the log, the log is now not being truncated.
> Any thoughts?
> Thanks.
As I said, I would suggestion NOT using the wizards... I've never
used that log shipping wizard, I have no idea what sort of backup job
it creates. Create your OWN processes, then you know what's going
on...