Log Shipping and extremely large .wrk file

9 Jul 2009 10:54 AM

Paul

Hi,
I have re-set up log shipping on a large database (124gig).

Because of the size of the database I used Quest Litespeed to back up the
database and move it to the remote server over a very slow link which took
almost 7 hours.

I restored the database so that additional log files could be restored.

I set up log shipping on the primary server and it detected the database
database was ready on the secondary server.

Problem:
The first TRN file created on the primary server is 151Gig!
TRNs after this (1 every 15 minutes) are around 4Mb.
The connection is very slow and it will take about a week and a half just to
copy this across the network.

Question:
I would expect the first TRN to be a bit larger as it is a days activity,
but larger than the whole database?

This database was previously set up for logshipping and this did not happen
- why is it happening now and how can it be avoided.

Thanks
Paul

9 Jul 2009 2:29 PM

TheSQLGuru

1) I recommend using Hyperbac. With this product you can use native
backup/restore syntax and thus it is completely seemless to use with log
shipping. You will have MUCH smaller tlog backups which will greatly help
WAN-based log shipping.

2) Why did you wait so long to do the first tlog backup? I would have been
doing them all along while the main full backup was being copied.

3) Did you by any chance have the database in FULL recovery mode prior to
doing log shipping setup but NEVER do a tlog backup? If you just did full
backups the tlog never got committed transactions flushed out of it. If
this is the case, you may want to do a NEW full backup and copy that and
restore it, then do log shipping and I bet the tlog backups will be MUCH
smaller.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Show quoteHide quote

"Paul" <P***@discussions.microsoft.com> wrote in message
news:A8311088-82A2-4FC4-952C-A303C7031CCD@microsoft.com...
>
> Hi,
> I have re-set up log shipping on a large database (124gig).
>
> Because of the size of the database I used Quest Litespeed to back up the
> database and move it to the remote server over a very slow link which took
> almost 7 hours.
>
> I restored the database so that additional log files could be restored.
>
> I set up log shipping on the primary server and it detected the database
> database was ready on the secondary server.
>
> Problem:
> The first TRN file created on the primary server is 151Gig!
> TRNs after this (1 every 15 minutes) are around 4Mb.
> The connection is very slow and it will take about a week and a half just
> to
> copy this across the network.
>
> Question:
> I would expect the first TRN to be a bit larger as it is a days activity,
> but larger than the whole database?
>
> This database was previously set up for logshipping and this did not
> happen
> - why is it happening now and how can it be avoided.
>
> Thanks
> Paul

9 Jul 2009 3:11 PM

Paul

The database is in full recovery mode and the Transaction log is currently
31mb.

Even if there are uncommited transactions, it doesn't explain a wrk file
27Gig larger than the database.

9 Jul 2009 3:48 PM

Tibor Karaszi

A log backup can definitely be (much)larger than the database size. It
contains all the modifications done since last log backup. Say you
modify all data in the database 10 times between two log backups -
that would cause the log backup to be 10 times larger than the amount
of data in the database (roughly - you get the idea). The bottom-line
here is when did you do your prior log backup and what
modifications/index rebuild etc was done since that.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Paul" <P***@discussions.microsoft.com> wrote in message
news:D240F1BD-4B4F-43C9-B516-5010A0E5F475@microsoft.com...
> The database is in full recovery mode and the Transaction log is
> currently
> 31mb.
>
> Even if there are uncommited transactions, it doesn't explain a wrk
> file
> 27Gig larger than the database.
>
>