0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user112359
  • 11
  • 2
  • 5
  • SQL Server 2012 RTM had a bug acknowledged by MS that made the log file grow a lot without reason in some situations, at least with simple recovery model. This got fixed in SP2 or SP3, if you are still in RTM or SP1 you can try installing the lasted SP. – Alberto Martinez Sep 08 '17 at 22:00

2 Answers2

1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nitin.sharma0180
  • 471
  • 3
  • 6
0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94