Tuncate log in log shipping SQL server to reduce size

Chong 21 Reputation points
2022-10-26T16:20:17.287+00:00

Hi Support,

We have 2 SQL servers running in log shipping and found the disk is out of space because the database log file too large, so we planned to truncate the DB log to reduce the size.

First, any way to reduce the DB log size but not break the log shipping?

If we truncate log in primary DB, seems the log shipping will break. We need to disable log shipping and remove the DB and log, then rebuild the log shipping? Can we just resume the log shipping by some configuration?

Thanks
Chong

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,949 questions
{count} votes

Accepted answer
  1. PandaPan-MSFT 1,916 Reputation points
    2022-10-27T05:43:34.437+00:00

    Hi @Chong ,

    This will also reduce the file size in secondary server, right?

    Normally yes, I tested it before and it worked so, but if you have configured your secondary in Norecovery mode then it won't work. You can check this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f4a788d-f633-41f2-8632-63f68e2a7b62/shrinking-data-file-on-primary-server-does-not-shrink-data-file-on-logshipping-server?forum=sqldatabaseengine

    We need to disable log shipping and remove the DB and log, then rebuild the log shipping? Can we just resume the log shipping by some configuration?

    Pls check the two links: https://dba.stackexchange.com/questions/69959/how-do-i-resume-log-shipping
    https://social.msdn.microsoft.com/Forums/en-US/e485033f-f2c1-4d1b-b672-ab812b28cd5a/log-shipping-how-to-resume-after-swap?forum=sqldisasterrecovery

    And I think that doing the FULL BACKUP may prevent accident happen.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,736 Reputation points
    2022-10-26T16:52:18.253+00:00

    The physical size of SQL Server files do not change unless you manually shrink them. Truncating the log is a "logical" operation which marks the data inside the files as reusable. It does not change the size of the physical file on the hard drive.

    See:
    https://www.mssqltips.com/sqlservertip/6888/shrink-database-sql-server/

    0 comments No comments

  2. Erland Sommarskog 112.7K Reputation points MVP
    2022-10-26T21:24:55.253+00:00

    To add to what Tom says, you can shrink the log file with DBCC SHRINKFILE without affecting log shipping.

    But to be able to shrink the file, there needs to be free space in the file. That is, you need to make sure that whatever that caused the log file to grow is gone.

    You don't have to take any special action to truncate the log. As Tom says, this is a logical action, and this occurs every time you back up the transaction log. Which you appears to be doing since you are running log shipping. But truncation only happens back to the oldest active page.

    To see why the transaction log does not truncate automatically, check the column log_reuse_wait_desc in sys.databases.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.