Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have setup log shipping on a database in Microsoft SQL Server 2012.
I was having issues with my secondary server and I found that this was probably due to a scheduled backup job messing up the log file consistency.
The solution was to set the job to do a copy_only backup however, now I am curious as to whether the log shipping will take care of the transaction log size, or will it just grow indefinitely?
Question: Will log shipping take care of truncating the log file?
The LSBackup job in a log shipping configuration performs a transaction log backup, so this will help maintain your log file size the same as a normal log backup job does.
Note that this will not truncate the file (reclaim space), it simply marks the portions of the log that have been backed up as available for re-use and this helps prevent log file growth by reusing existing space in the file. For performance reason, you don't want to be shrinking and regrowing your transaction log file constantly.
Typically if you're using log shipping there is no need to run another copy-only log backup job, you're better off simply copying the log backups that LSBackup creates into a suitable storage location. You will also run into problems if you're running LSBackup and a non-copy only log backup job as the restore chain will be broken on the secondary, as you've found.
Log shipping doesn't "take care" of truncating the log files. The backups of the transaction log allow re-use (not truncation) of the non-active Virtual Log Files (VLF). Log shipping doesn't ship the logs, it ships the backups of the logs to the secondary where they can be restored. The process is pretty involved, I highly recommend that you thoroughly read this article before deciding on making changes such as switching to copy only.
Unfortunately terminology in the SQL server worlds isn't as ... precise and we wish for. The phrase "truncate the log" has been used for a long long time to mean "mark old VLFs as OK to re-use" (or, "empty the log" as I prefer to say when I want to keep it simple). The phrase has roots in the days when the log was a table, and you "truncated" that table when you did a log backup. As of 7.0, the architecture is difference, hence the phrase "truncate the log" can be mis-leading. But, if you mean "empty the log, like a regular log backup does", then yes. Log shipping does just that: a regular log backup.
If you mean "make the file smaller", then: no. No backup operation attempts to shrink any type of database file. That is what the DBCC SHRINK% commands are for.
truncateSQL Server / Oracle is not related to log truncation. The truncate tag is reserved for the DDL operation and should not be confused with Log Truncation with the truncation tag.