SQL Server 2016 - Tran log growing huge

Question

SQL Server 2016 - Tran log growing huge

JSB_89

Ten Centuries

Points: 1075
More actions
June 2, 2018 at 12:17 am

#413555

Forum,
We recently deployed a new server SQL 2016 (first)and built an Availability group, now the problem is the TRN log files are growing huge. I have tran logs backed up hourly(is it a best practice hourly or less/more frequently) and a full backup on the DB on Friday's as the DB size is almost 2TB.
I see the TRN logs grow and i have to manual shrink the files ,
Log file initial size for all DB's on AG is set to 1GB -- is this a recommended way ?
July 1, 2018 at 11:08 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

jampabsatish - Saturday, June 2, 2018 12:17 AM
Forum,
We recently deployed a new server SQL 2016 (first)and built an Availability group, now the problem is the TRN log files are growing huge. I have tran logs backed up hourly(is it a best practice hourly or less/more frequently) and a full backup on the DB on Friday's as the DB size is almost 2TB.
I see the TRN logs grow and i have to manual shrink the files ,
Log file initial size for all DB's on AG is set to 1GB -- is this a recommended way ?

1GB is pretty small for a 2TB database with AG. What size is the log file growing to?

Either way, you might want to stop annoying yourself with manual shrink files... until you find and fix the problem (if there is one), it's just gonna grow again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

I agree with Jeff; stop doing the manual shrinks. Well, do a final one and then start taking your transaction log backups more frequently, say every 10 or 15 minutes to start with.

dwalker-543874 SSC Veteran Points: 228 More actions · Answer 3

The transaction log growing for a database participating in an AG is a sign that the secondary replica had not caught up at the time of your trans log backup. The backup will complete successfully; however, the trans log will not truncate. When this happens you can run the following query and see the reason the trans log did not truncate:

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

If the reason is "Availability Replica", then it is waiting for your secondary replica to catch up. Running more frequent trans log backups might help but they might not too, it depends on how slow the latency is between the primary and the secondary. I would not shrink transaction logs unless space is an issue. Transaction log file growth can negatively impact performance. The frequent shrinking can cause the number of VLFs within the trans log to grow and thus cause fragmentation within the log file, which can also negatively affect performance. It's best to have the trans log pre-sized to its largest size.

If the reason is indeed availability replica waits, check the usual suspects (network and storage) to see if that can be improved.

DHeath SSCertifiable Points: 5200 More actions · Answer 4

The transaction log growing is just part of the whole equation. I think you should set a max size on the Tlog file and let it grow...i agree with Jeff that 1GB is rather small for a 2TB database. I would think you could set it to at least 10-20GB and give the replica and everything a chance to do its thing especially since its the initial setup or run. Also the manual shrink is very temporary as you have seen and the files just grows back.
Take a full backup and tlog backup ... then shrink your files(one last time) and reset your ldf to about 10-20GB and then do your tlog backups every 60-90 minutes (if the space is adequate)
Just my .02 and good luck

DHeath

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Doing more frequent log file backups

Lj Burrows - Sunday, July 1, 2018 11:08 PM
Shrinking is bad! Shrinking database files causes blocking when it runs. It takes alot of resources and cost performance.The only time transaction log should be shrunk if there is abnormal database activity resulted in log growing fast. Shrinking at regular basis result in log growing again and again. You can avoid this practice by taking regular transaction log. Here is the method to shrink your transaction log file. Also see,some tips to stop growing log file too big.
http://mahedee.net/how-to-stop-growing-log-file-too-big/

Doing more frequent log file backups can certainly help manage the growth of log files if nothing is out of whack on the system. However, that alone will not keep the size of the log file down. For example, if you rebuild large indexes in the FULL Recovery, doing more frequent backups will not help because each rebuild is done as a single transaction. Even if you're using the SIMPLE Recovery Model, performance challenged code can cause explosive growth of the log file for the same reason.

I agree that more frequent log file backups won't hurt anything and I recommend doing so if you actually value your data but that alone won't fix things if you have unnecessarily large transactions. You need to find and fix those problems, especially for TempDB, which you can't actually do log file backups on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JSB_89 Ten Centuries Points: 1075 More actions · Answer 6

Lj Burrows - Sunday, July 1, 2018 11:08 PM
Shrinking is bad! Shrinking database files causes blocking when it runs. It takes alot of resources and cost performance.The only time transaction log should be shrunk if there is abnormal database activity resulted in log growing fast. Shrinking at regular basis result in log growing again and again. You can avoid this practice by taking regular transaction log. Here is the method to shrink your transaction log file. Also see,some tips to stop growing log file too big.
http://mahedee.net/how-to-stop-growing-log-file-too-big/

Thanks Lincoln, we have been taking regular hourly trn log backups .. and bi-weekly full backups .. my DB's are ~ 4TB each and i cannot take daily full backups .

JSB_89 Ten Centuries Points: 1075 More actions · Answer 7

Jeff Moden - Monday, July 2, 2018 6:38 AM
Doing more frequent log file backups
Lj Burrows - Sunday, July 1, 2018 11:08 PM
Shrinking is bad! Shrinking database files causes blocking when it runs. It takes alot of resources and cost performance.The only time transaction log should be shrunk if there is abnormal database activity resulted in log growing fast. Shrinking at regular basis result in log growing again and again. You can avoid this practice by taking regular transaction log. Here is the method to shrink your transaction log file. Also see,some tips to stop growing log file too big.
http://mahedee.net/how-to-stop-growing-log-file-too-big/
Doing more frequent log file backups can certainly help manage the growth of log files if nothing is out of whack on the system. However, that alone will not keep the size of the log file down. For example, if you rebuild large indexes in the FULL Recovery, doing more frequent backups will not help because each rebuild is done as a single transaction. Even if you're using the SIMPLE Recovery Model, performance challenged code can cause explosive growth of the log file for the same reason.
I agree that more frequent log file backups won't hurt anything and I recommend doing so if you actually value your data but that alone won't fix things if you have unnecessarily large transactions. You need to find and fix those problems, especially for TempDB, which you can't actually do log file backups on.

Thanks Jeff !!

Sue_H SSC Guru Points: 90860 More actions · Answer 8

You still need to dig in and find what is causing the issue. Have you checked the log_reuse_wait_desc in sys.databases as previously suggested? That could certainly help in tracking down the reasons. This article identifies different scenarios cause log growth.
A transaction log grows unexpectedly or becomes full in SQL Server

It has a section called
AlwaysOn 'AVAILABILITY_REPLICA' applying transaction log records to a secondary database
which is something you would want to check as well.

Sue

Alejandro Santana SSCommitted Points: 1835 More actions · Answer 9

You should check if your AG is sending the transactions to the secondary replica.. when you backup log if any of these transactions haven't been sent to the secondary replica it doesn't clean the log it only cleans the transactions that have been sent.

All the transactions will stay in your log until everything has been sent off to the secondary replica.

I'll try to dig which system tables are the ones that hold the data to be sent to the secondary replica.