This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello,
I have a SQL Server 2016 server with multiple SQL Server instances acting as primary server in a log shipping configuration. I have configured log shipping as per https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-2016.
All databases are log shipped (in standby mode with read-only option enabled) to a secondary server and the log shipping process (transaction log backups to .trn files, copy to secondary and restore to secondary) seems to be working without issues. I also have a third party tool which takes only full database backups every 24hours. Transaction log backups are only managed by the log shipping process. However i see that the transaction log file size is constantly growing, which most likely means that the transaction log is not being truncated after every transaction log backup. This did not occur before I configured log shipping in this environment, in which case the third party tool was also taking care of the transaction log backups and the log sizes were kept to low/normal.
After reading https://dba.stackexchange.com/questions/224711/does-mssql-log-shipping-truncate-the-transaction-log , i realize that truncating the transaction logs seems to not be part of the log shipping process by design. But this contradicts with the fact that the log shipping process is supposed to be taking transaction log backups, which by design are supposed to truncate the logs.
If this is by design, then what is the recommended approach for keeping the log file sizes to low/normal, while also not breaking the log shipping process? I would like to listen to other people's views but also, ideally, some guidance from Microsoft on this, since I have not been able to find anything in the official documentation.
As for why the transaction logs is not truncated, check the column log_reuse_wait_desc in sys.databases. There could be other reasons why the log is not truncated.
Apart from that, I can't say that I am happy with this arrangement. In case there is a disaster, and a database needs to be restore to a point in time. Do you know how to do that with help of the full backup from the third-party tool and the logs from the log-shipping?
If you are confident with that process, fine. If you are not - turn off log-shipping and enable log backups in the external tool immediately!
Log backups will truncate the log, provided that nothing else blocks truncation. There is one exception: You take the log backup with COPY_ONLY. Which is good so far that it does not interfere with with the third-party tool. But if the third-party tool truncates the log, the log-shipping will soon have a broken log chain.
After reading https://dba.stackexchange.com/questions/224711/does-mssql-log-shipping-truncate-the-transaction-log , i realize that truncating the transaction logs seems to not be part of the log shipping process by design.
That post talks about the size of the log file not shrinking. Truncation only means that space in the log is freed up for re-use, but the file size remain the same, unless you actively shrink it.
Regarding the growth of transaction logs in Log Shipping, you need to timely back up the transaction logs on the primary server.
Avoid using operations like DBCC SHRINKFILE on the log file, as this can lead to fragmentation and may affect performance. Instead, focus on regular log backups to effectively manage the size of the logs.
It is recommended to manage backup operations to ensure that only one process is handling log backups and truncation at any given time.
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".