Post reply

Transaction log shrink w/ logshipping

  • August 2, 2021 at 7:40 am

    #3916027

    Hello everyone,

    Again with this question. 🙂

    I've got a log-shipped database, and I would like to decrease the size of the transaction log.

    What's different from the other examples of this question I've seen here is that I do know that the current size of the transaction log was caused by a single large transaction which will not be repeated any time soon.

    So as I said: I would like to get my TLog back to the 30 GB it's been for the last 6 years or so, instead of the 240GB it is now.

    Can I safely use the 'truncateonly' option on the transactionlog (without killing the log-shipping)?

    In all examples of this question there have been comments about not shinking the log except in case of exceptional transactions, but I've yet to see a clear go/nogo response if the issue _is_ known to be caused by an exceptional transaction.

    Kind regards,

    Martin

    • This topic was modified 2 years, 9 months ago by  mavelzen.

  • August 2, 2021 at 10:23 am

    #3916079

    Hi,

    have you checked if your transaction log is empty?

    dbcc sqlperf (logspace)

    If you log is empty enough, then you should able to shrink it.

    Please check before, if it is possible to shrink:

    SELECT
        name AS DatabaseName,
        log_reuse_wait_desc AS LogBlocking,
        recovery_model_desc AS RecoveryModel
    FROM sys.databases
    WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'maintenancedb') AND log_reuse_wait_desc NOT LIKE 'NOTHING'

    Goodl luck,

    Andreas

  • August 2, 2021 at 11:09 am

    #3916092

    Andreas,

    Thank you for your reply.

    I would think there is space to be reclaimed:

    ss_spaceInLog

    which makes sence, as the database is logshipped and there are no big transactions running.

    The other query you sent doesn't return the database I'm interested in, does this mean the log can or cannot be shrunk.

    Kind regards,

    Martin

  • August 2, 2021 at 11:33 am

    #3916105

    Hi,

    if there are no entrys, there are no reasons that should block the log backup. And if the log backup is running, you should be able to shrink your database.

    But, don't shrink to much in one step. Just test it with small steps.

    Andreas

  • August 2, 2021 at 12:36 pm

    #3916125

    I would just run DBCC SHRINKFILE(2, 30720)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply