Share via

Why wont my log backups shrink the log file?

Mark Hayward 41 Reputation points
2020-11-27T11:50:25.047+00:00

Hi,

I have a database with a half a terrabyte log file. It says the log is 96% full. It wont shrink and I cant put it in simple mode because it is involved in replication.
I take log backups every 2 minutes. I have done a full backup and the log still wont shrink..

I thought the log should empty after a transaction log backup... is this not the case?

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,361 questions
Accepted answer
  1. Shashank Singh 6,251 Reputation points
    2020-11-27T12:05:40.19+00:00

    take log backups every 2 minutes. I have done a full backup and the log still wont shrink..

    This is because your replication might not be working properly. What is output of

    select log_reuse_wait_desc from sys.databases where name=''db_name

    My hunch, replication.

    Is your log reader agent not working, I am sure you have some issue with replication and since you say your have 500GB log file I would say fixing replication and moving those 500GB log on subscriber would not be good idea altogether as it would take time. But you are the best judge. My saying is you might need to reinitialize the replication

    I thought the log should empty after a transaction log backup... is this not the case?

    Transaction log backup would mark transaction log blocks as reusable ONLY IF no other process needs that lock or is holing the lock. The process can be log backup, replication, Availability groups etc.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mark Hayward 41 Reputation points
    2020-11-27T12:10:35.453+00:00

    The output of that is 'REPLICATION'

    I reset the replication when this happened several weeks ago. But the log just grew again. It never seems to release the space