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

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 (AT) hotmail (DOT) com> wrote

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 (AT) shadhawk (DOT) com> wrote

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 (AT) hotmail (DOT) com> wrote

Thanks Andrew.


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Just wondering, In log shipping environment, the log will auto shrink or
never shrink?


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

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 (AT) hotmail (DOT) com> wrote

So is that good if the Data File size is 2GB and Log File size is 7 GB.... I
am trying to understand, in log shipping environment we can't shrink the log
file then it will go to maximum size to our hard disk.




"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

You can shrink the log file if you need to. What I am saying is that if it
continues to grow that large you have a problem somewhere.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding

--
Andrew J. Kelly SQL MVP

"Rogers" <naissani (AT) hotmail (DOT) com> wrote