Re: Log Shipping....

Log files never shrink unless you issue a shrink command or have the
autoshrink option turned on. If you issue regualr log backups and don't have
long running open transactions the log file can reuse the parts of the log
that have been committed and backedup.

--
Andrew J. Kelly SQL MVP

"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:%23iOXzzCgHHA.5056@xxxxxxxxxxxxxxxxxxxxxxx
Just wondering, In log shipping environment, the log will auto shrink or
never shrink?


"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:upSAYxgfHHA.4704@xxxxxxxxxxxxxxxxxxxxxxx
I have no idea what environment you have. Is it the log backups that are
140GB in size of the transaction log itself? If it is the transaction log
file then you most likely have one of two conditions. Either you did not
start with a valid FULL backup or you have an open transaction that is
preventing the log from truncating. What does DBCC OPENTRAN() show for
that db?

--
Andrew J. Kelly SQL MVP

"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:%23XfwtzdfHHA.3648@xxxxxxxxxxxxxxxxxxxxxxx
There is a log shipping environment and I can't change any thing, if you
can let me know if change the intervel time from 15 to 20 mins, will
that helpful ?

Thanks

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:OZPoY9SfHHA.4872@xxxxxxxxxxxxxxxxxxxxxxx
What are you doing? Are you backing up tot he same log file (device)
each time? If so stop that:). Backup to a different file each time by
adding a timestamp to the file name. Then the backups should be
relatively small and you can delete them after a specified time period.

--
Andrew J. Kelly SQL MVP

"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:%23nibvnRfHHA.1456@xxxxxxxxxxxxxxxxxxxxxxx
Log Shipping....

In my organization we had implemented Disaster Recovery (Log Shipping)
but Log File size is increasing gradually (140 GB) and Data File size
is 20 GB, I am just wondering log file size should be trancate once it
will ship to destination Server,

FYI, log ship every after 15 mins.

I would appreciate if any one can guide me how log could be shrink
automatically.

Thanks











.

Relevant Pages

  • Re: how often autoshrink?
    ... Autoshrink is not a very good option to enable, ... attack the log file. ... because under typical usage the file will shrink then you will need it to ... For databases that are not mission critical (e.g. full backups, ...
    (microsoft.public.sqlserver.programming)
  • Re: Restoring DB**
    ... is 6GB and your db is only 450MB you are not doing backups properly. ... That will keep the log file from growing. ... http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File ... > with move option to modify while location restore database is terminating> abnormally." ...
    (microsoft.public.sqlserver.programming)
  • Re: dbcc reindex & dbcc shrinkdatabase?
    ... An increase in the data file is acceptable but not the log file. ... started testing out is scheduling the "dbcc reindex", ... not much free space to reclaim. ... this does indeed shrink the virtual logs size, ...
    (microsoft.public.sqlserver.server)
  • Re: Database/Logs dont shrink with Maintenance Task
    ... Only a shrink file can do that but it may require a backup to get ... Other wise it has to keep growing to make room for more transactions. ... the log file smaller--whether you shrink or not? ...
    (microsoft.public.sqlserver.setup)
  • Re: Cant shrink log file
    ... model and don't do log backup. ... Either schedule regular log backups or set recovery model to simple. ... I was trying both and still it doesn't shrink. ... Cannot shrink log file 2 because the logical log file located at the end of the file ...
    (microsoft.public.sqlserver.server)
Loading