SQL Server Log Files growing rapidly

Question

We have SQL Server 2012 (on Windows Server 2012 R2) and a database configured for AlwaysON (01-03) and we take full backups once a day and log backups every 5 minutes. The size of the database is 288 GB (mdf - 88GB, ndf - 110 GB, ldf - 84 GB).

There are no long transactions. I shrunk the log files twice and bought down to 4 GB, but within 2 days they're back up to 88 GB.

How can I fix this?

Answer 1

I shrinked log files twice and bought down to 4GB

Since you were able to shrink log and the log file size got reduced,the log growth you are seeing might be due to high volume of transactions..

below is the way to find out which transactions are causing log growth...

select 
    txt.text,
    sum(trn.database_transaction_log_bytes_used + 
        trn.database_transaction_log_bytes_reserved) as totallogused 
from 
    sys.dm_tran_database_transactions trn
join
    sys.dm_exec_requests ec on ec.transaction_id=trn.transaction_id
cross apply
    sys.dm_exec_sql_text(EC.sql_handle) txt
group by 
    txt.text

Answer 2

As per my understanding some of the reasons the transaction log file can grow are:

1. Long running queries:

   select name, log_reuse_wait_desc 
   from sys.databases  
   

   This should show why the transaction log file is not releasing the space

2. Optimization job runs, may also cause transaction log file to grow

3. Log backup frequency set improperly - some time the best option would be in case of before any optimization job runs - check the Log file usage - if you need set an log backup before any optimization job runs

Having proper log backups scheduled is the best way of dealing with log growth unless for an unusual situation.