Shrink a database log file which is synchronized (Always on Group)

Sai 126

How to shrink a database log file which is synchronized(Always on Group).
SQL version: SQL server 2017 Enterprise edition

I have seen huge growth in log file(mdf- 500GB and ldf - 800GB).
Preforming transactional log backup every 30 mins and full backup every day night.
But didn't see any improvement from 1 week.

Please suggest.

7 answers

Sai 126 Reputation points

2021-11-21T07:15:37.53+00:00

I checked with developers, They create and delete lots of tables weekly. They were asking us to delete logs during the maintenance.

Can I ask them, Delete large number of rows and tables without growing the transaction log?

Will it help instead of planning to shrink database log file regularly?

This database is in always on group and with Full recovery model.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Ronen Ariely 15,096 Reputation points

2021-11-21T11:18:07.147+00:00

Delete large number of rows and tables without growing the transaction log?

DELETE rows is a transactional action which log all the information, but <As Erland mentioned> DROP table is mainly a metadata action which logs only a few rows in the transaction log. Moreover, It does not really delete any data from the data file as well (the data place is re-useable but at the DROP point in time it is still on the disk).

Another action which you can take if you want to delete all the rows is TRUNCATE TABLE, which also is a metadata action.

It should be faster to TRUNCATE 1000000000000 rows than to DELETE 100000 usually, and log file will not need to grow.

Note! focus on Erland explanation and don't be harry to take the first idea that come to your mind if you have issue. Transaction log is big => many jump to confusion or fast wrong recommendation to make it smaller instead of thinking about the impact of such action.

Even when someone ask "how to shrink" the answer is usually NOT the explanation on how to shrink but on how to avoid the need to shrink!

It is what call XY problem. You ask about X while the source of the issue is y, and you should have solve y and not X.
https://en.wikipedia.org/wiki/XY_problem

In general, if you came to a situation that you need to shrink your files regularly, then you need to take 2 step back and think about your system design.
Please sign in to rate this answer.
Erland Sommarskog 101.8K Reputation points MVP

2021-11-21T11:33:34.01+00:00

In general, if you came to a situation that you need to shrink your files regularly, then you need to take 2 step back and think about your system design.

The situations where you need to shrink log files are very rare. A Swedish MVP colleague once had a talk where he suggested that you could do this if you are short on disk space, and you have multiple databases. That is, you let one log file grow. Shrink that log, and then perform a log-growing operation on the next database. Obviously, that's a management nightmare, and only acceptable as a makeshift solution until you get more disk.

On the other hand, my experience from monitoring SQL forums, that it is uncommon to see people who think they need to shrink the log regularly. But because the log is being truncated on backup and the area being reused in a circular action there is no need to. Once the log has grown to a size needed for the workload, it will stay at that size. Although, you may need to check for things like orphaned transactions that can cause problems. If you have an AG there are a few more things that can go wrong.
Sign in to comment