Log shipping vs shrinking Log Files

TYzermans

I have a customer who uses log shipping.

He shrinked the transaction log of a database.

Is the log shipping still working after a file-shrink for the log files and if not, what is the eaiest way to re-enable the log shipping?

Thanks for the help,

Regards,
Timothy Yzermans

DenSter

I don't think one has anything to do with the other. Did you observe that log shipping stopped working though?

By the way, you should only shrink the transaction log if it has grown exceptionally large, and you want to reclaim some of the space. Shrinking the log as a regular task is not recommended. It will fragment the file, because it will grow again suring daily use. It also causes performance hits when the system grows the file, which is usually at the busiest times.

TYzermans

No did not observe it stopped working.

No the whole story is that the client gave me call this afternoon telling me his log file was growing over 40Gb (for a NAV test database) and he asked me what he can do to shrink this file. I told him, if his daily backup was running without errors, he could try to shrink his transaction log file (since it is a test database he wouldn't have to wurry about restoring transactions).

After a while he called me that it worked but then he told me that they use log shipping and I am/was not shure how it will affect the log shipping.

Thanks for your answer.

Regards,
Tim

stryk

Hi!
Normally the shrinking of the TLog file - e.g. using the DBCC SHRINKFILE command - will not affect the Log Shipping. But this shrinking is only possible if the TLog has been truncated by a BACKUP LOG before, means the committed data has been saved to the backup file and the space has been released.

If you clear the TLog by using a BACKUP LOG TRUNCATE ONLY command, then also the committed data would be removed, but NOT saved into a backup file!
In this case you interrupt the consistent chain of backups and you need to restart the Log Shipping by copying a new FULL backup!

Regards,
Jörg

TYzermans

stryk wrote:

Hi!
Normally the shrinking of the TLog file - e.g. using the DBCC SHRINKFILE command - will not affect the Log Shipping. But this shrinking is only possible if the TLog has been truncated by a BACKUP LOG before, means the committed data has been saved to the backup file and the space has been released.

If you clear the TLog by using a BACKUP LOG TRUNCATE ONLY command, then also the committed data would be removed, but NOT saved into a backup file!
In this case you interrupt the consistent chain of backups and you need to restart the Log Shipping by copying a new FULL backup!

Regards,
Jörg

Dear Jörg,

Thanks for your answer!

One final question: when performing the shrink from the SSMS. Right-click on the database -> tasks -> Shrink -> Files. Then choose "Log" at File type. Is this procedure the same as the DBCC SHRINKFILE or is it the same as the BACKUP LOG TRUNCATE ONLY?

Thanks..
Regards,

Timothy

stryk

Well, if you execute the "Right-click on the database -> tasks -> Shrink -> Files the Log" then, depending on the "Option" you selected, either a DBCC SHRINKFILE (N'Navision_Log' , 0, TRUNCATEONLY) or a DBCC SHRINKFILE (N'Navision_Log' , 2000) or a DBCC SHRINKFILE (N'Navision' , EMPTYFILE).
SO it's all a DBCC SHRINKFILE. PLease regards that the TRUNCATEONLY option of the DBCC is a different thing than the BACKUP LOG TRUNCATE_ONLY:
The DBCC just moves the data and shrinks the file; a BACKUP LOG TRUNCATE_ONLY will erase the data!
(See "Books Online" for details)

Regards,
Jörg

P.S.: In SSMS you could oftenly find a button "Script" on top of a form, e.g. when doing the "Shrink File". By clicking on that the TSQL for this operation is generated, so you could see what's actually going on!