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

Thanks in advance.

Deep

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?




"Deep" <khandelwal.deep@gmail.com> wrote in message
news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>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
>
> Thanks in advance.
>
> Deep

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:42pm, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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?
>
> "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>
> >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
>
> > Thanks in advance.
>
> > Deep

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.





"Deep" <khandelwal.deep@gmail.com> wrote in message
news:380d5642-b0aa-4ffc-b7ae-fbb7324f5769@l42g2000hsc.googlegroups.com...
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
> 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?
>
> "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>
> >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
>
> > Thanks in advance.
>
> > Deep

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 ................



On Jul 16, 7:07pm, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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.
>
> "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> news:380d5642-b0aa-4ffc-b7ae-fbb7324f5769@l42g2000hsc.googlegroups.com...
> 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
> > 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?
>
> > "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> >news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>
> > >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
>
> > > Thanks in advance.
>
> > > Deep

> 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?

--
Ekrem Önsoy




"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23mbrrm05IHA.1428@TK2MSFTNGP06.phx.gbl...
> 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?
>
>
>
>
> "Deep" <khandelwal.deep@gmail.com> wrote in message
> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>>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
>>
>> Thanks in advance.
>>
>> Deep
>
>

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


"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news:30518432-0F15-4EAB-9051-67F620987945@microsoft.com...
>> 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?
>
> --
> Ekrem Önsoy
>
>
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:%23mbrrm05IHA.1428@TK2MSFTNGP06.phx.gbl...
>> 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?
>>
>>
>>
>>
>> "Deep" <khandelwal.deep@gmail.com> wrote in message
>> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>>>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
>>>
>>> Thanks in advance.
>>>
>>> Deep
>>
>>
>

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.

--
Ekrem Önsoy



"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23UHFjl$5IHA.2240@TK2MSFTNGP02.phx.gbl...
> 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
>
>
> "Ekrem Önsoy" <ekrem@compecta.com> wrote in message
> news:30518432-0F15-4EAB-9051-67F620987945@microsoft.com...
>>> 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?
>>
>> --
>> Ekrem Önsoy
>>
>>
>>
>>
>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>> news:%23mbrrm05IHA.1428@TK2MSFTNGP06.phx.gbl...
>>> 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?
>>>
>>>
>>>
>>>
>>> "Deep" <khandelwal.deep@gmail.com> wrote in message
>>> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>>>>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
>>>>
>>>> Thanks in advance.
>>>>
>>>> Deep
>>>
>>>
>>
>
>

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.

--
Ekrem Önsoy



"Deep" <khandelwal.deep@gmail.com> wrote in message
news:d46887a5-08d2-4824-ad5c-fa571dd23944@e53g2000hsa.googlegroups.com...
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 ................



On Jul 16, 7:07 pm, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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.
>
> "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> news:380d5642-b0aa-4ffc-b7ae-fbb7324f5769@l42g2000hsc.googlegroups.com...
> 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
> > 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?
>
> > "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> >news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>
> > >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
>
> > > Thanks in advance.
>
> > > Deep

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




"Ekrem Önsoy" <ekrem@compecta.com> wrote in message
news:17608695-7633-4210-9F79-2BF0384213B3@microsoft.com...
> 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.
>
> --
> Ekrem Önsoy
>
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:%23UHFjl$5IHA.2240@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>>
>> "Ekrem Önsoy" <ekrem@compecta.com> wrote in message
>> news:30518432-0F15-4EAB-9051-67F620987945@microsoft.com...
>>>> 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?
>>>
>>> --
>>> Ekrem Önsoy
>>>
>>>
>>>
>>>
>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>> news:%23mbrrm05IHA.1428@TK2MSFTNGP06.phx.gbl...
>>>> 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?
>>>>
>>>>
>>>>
>>>>
>>>> "Deep" <khandelwal.deep@gmail.com> wrote in message
>>>> news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com...
>>>>>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
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>> Deep
>>>>
>>>>
>>>
>>
>>
>

Hi URI and Ekrem,

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

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



On Jul 17, 4:08pm, Ekrem Önsoy <ek...@compecta.com> wrote:
> 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.
>
> --
> Ekrem Önsoy
>
> "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> news:d46887a5-08d2-4824-ad5c-fa571dd23944@e53g2000hsa.googlegroups.com...
> 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 ................
>
> On Jul 16, 7:07 pm, "Uri Dimant" <u...@iscar.co.il> wrote:
>
> > 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.
>
> > "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> >news:380d5642-b0aa-4ffc-b7ae-fbb7324f5769@l42g2000hsc.googlegroups.com....
> > 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
> > > 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?
>
> > > "Deep" <khandelwal.d...@gmail.com> wrote in message
>
> > >news:28c1f993-c0fe-4e69-ad85-f18c071a8f79@m3g2000hsc.googlegroups.com....
>
> > > >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
>
> > > > Thanks in advance.
>
> > > > Deep