Handling Log File after implementing Log Shipping.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deep - 16 Jul 2008 14:31 GMT

I have implemented log shipping between A production Server say PROD1
database and two DR server's DR1 and DR2.

After implementing log shipping LOG file in Prod1 started to increase,
with in 3 days it has reached to 40 GB.

As amount of transactions on my production database is very high.

After taking log backup (log shipping) physical file is not
decreasing.

What should i do to handle this increasing log file problem.

Please help

Thanks in advance.

Deep

Reply to this Message

Uri Dimant - 16 Jul 2008 14:42 GMT

Deep
Log backup does NOT truncate the log file. In order to reduce physical size
of the log, use DBCC SHRINKFILE

> What should i do to handle this increasing log file problem.

How often do you backup log file?

>I have implemented log shipping between A production Server say PROD1
> database and two DR server's DR1 and DR2.
[quoted text clipped - 14 lines]
>
> Deep

Reply to this Message

Deep - 16 Jul 2008 14:55 GMT

Hi Uri,

As i have implemented Log Shipping i am not taking log backup
manually. Log Backup happens every 15 Mins and get shipped to the DR
Servers.

If i use DBCC ShrinkFile to Shrink the logs the lsns will not remain
in sync and the Log Shipping will get failed.

How to handle this problem.

> Deep
> Log backup does NOT truncate the log file. In order to reduce physical size
[quoted text clipped - 22 lines]
>
> > Deep

Reply to this Message

Uri Dimant - 16 Jul 2008 15:07 GMT

Deep
Well, perhaps you need to stop log shipping and shrink the file. I'd
suggest you to examine your transactions, perhasps somewhere there are
opened/long running transactions.

Hi Uri,

As i have implemented Log Shipping i am not taking log backup
manually. Log Backup happens every 15 Mins and get shipped to the DR
Servers.

If i use DBCC ShrinkFile to Shrink the logs the lsns will not remain
in sync and the Log Shipping will get failed.

How to handle this problem.

On Jul 16, 6:42 pm, "Uri Dimant" <u...@iscar.co.il> wrote:

> Deep
> Log backup does NOT truncate the log file. In order to reduce physical
[quoted text clipped - 27 lines]
>
> > Deep

Reply to this Message

Deep - 17 Jul 2008 06:46 GMT

So URI,

That means there is no other way to solve this problem. Log files can
not be shrinked until i stop Log Shipping....?, But its very
problematic for me to configure log shipping every 5 or 6 days. There
must be some way.....

Please Help................

> Deep
> Well, perhaps you need to stop log shipping and shrink the file. I'd
[quoted text clipped - 45 lines]
>
> > > Deep

Reply to this Message

Ekrem Önsoy - 17 Jul 2008 12:08 GMT

Is your Log Shipping partners (Primary and Secondary) working without any
problem?

Have you performed a rebuild index operation or a bulk insert recently?
Because these operations would cause the log file expand.

And if you experience a problem with your secondary partner, then
transactions would be kept in the Primary server' s log file if the Log
Shipping is still active untill these transactions which are pending to be
sent to the secondary server is sent.

Taking Log Backups other than the Log Shipping's would break the chain and
your Log Shipping fails. And as your logs are already being backed up by Log
Shipping jobs, you don't need to perform this action to empty your log file.

I suggest you to Shrink your database according to your needs. You did not
mention if this is a SQL Server 2005 or not. If it's, then open up SSMS and
then right click on your database then Tasks\Shrink\Files. "Shrink File"
window will appear. From the File Type, choose Log. There, you'll see
Available free space in your log file. I suggest you to use "Reorganize
pages before releasing unused space" option and shrink your log file to a
certain size according to your needs. Don't let your log file to grow and
shrink all the time. Give it some rooms so that it'll not be expanded
continually and you'll not suffer from performance issues that can occur
because this operation.

Signature

Ekrem Önsoy

> Deep
> Well, perhaps you need to stop log shipping and shrink the file. I'd
[quoted text clipped - 46 lines]
>
> > > Deep

Reply to this Message

Deep - 17 Jul 2008 12:29 GMT

Hi URI and Ekrem,

Sorry for not mentioning the DB Server its SQL server 2005 Std. Ed..

And thanks for the help now i m clear how to manage.

> Is your Log Shipping partners (Primary and Secondary) working without any
> problem?
[quoted text clipped - 86 lines]
>
> > > > Deep

Reply to this Message

Ekrem Önsoy - 17 Jul 2008 10:43 GMT

> Log backup does NOT truncate the log file. In order to reduce physical
> size of the log, use DBCC SHRINKFILE

Actually, BACKUP LOG truncates the log file however it does not change the
physical file size.

Or did I misunderstand you?

Signature

Ekrem Önsoy

> Deep
> Log backup does NOT truncate the log file. In order to reduce physical
[quoted text clipped - 22 lines]
>>
>> Deep

Reply to this Message

Uri Dimant - 17 Jul 2008 11:40 GMT

Hi
No, BACKUP LOG just 'empties' (commited transactions) virtual log files (
log file is built/divided with virtual logs). Meaning the purpose is to make
VL reusable for new transactions.

Again, to reduce physical size of the LOG you need issue DBCC SHRINKFILE
command

>> Log backup does NOT truncate the log file. In order to reduce physical
>> size of the log, use DBCC SHRINKFILE
[quoted text clipped - 30 lines]
>>>
>>> Deep

Reply to this Message

Ekrem Önsoy - 17 Jul 2008 12:08 GMT

Well, I know that quite good. The point that I wanted to clary was BACKUP
LOG would empty the passive virtual logs inside the Transaction Log file.

I needed to mention that, because you said "Log backup does NOT truncate the
log file. In order to reduce physical size of the log". Taking log backup
truncates the LOG file (this is where I tried to touched) but of course it
does not reduce the size of the physical log file.

Signature

Ekrem Önsoy

> Hi
> No, BACKUP LOG just 'empties' (commited transactions) virtual log files
[quoted text clipped - 38 lines]
>>>>
>>>> Deep

Reply to this Message

Uri Dimant - 17 Jul 2008 12:27 GMT

Hi
It does matter how we interpritate TRUNCATE word:-)
I use EMPTY (VL) and not truncate log file , but you mean TRUNCATE VL.

> Well, I know that quite good. The point that I wanted to clary was BACKUP
> LOG would empty the passive virtual logs inside the Transaction Log file.
[quoted text clipped - 46 lines]
>>>>>
>>>>> Deep

Reply to this Message