Home Forums SQL Server 2012 SQL 2012 - General Transaction log is not shrink although there is scheduled transaction log backup RE: Transaction log is not shrink although there is scheduled transaction log backup

  • Backing up the log won't decrease the file size of the log itself. It frees space within the log of committed transactions. If your log is continuing to grow, I suspect you may have either extremely large transactions, or you have uncommitted transactions that are holding on to their space. You should check sys.dm_tran_active_transactions to see if you have open transactions. You can combine that with other DMVs such as sys.dm_exec_requests and sys.dm_exec_sql_text to see the query, blocked/blocking processes, resources waits, etc.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning