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

Question

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.

Answer 1

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

Absolutely not! Shrinking a file - data or log - is an exceptional event. It is something you do after has been an accident that cause a file to grow, or, in case of a data file, you have purged a lot data. Shrinking a log file could also be relevant if you have taken an action that you know will reduced the amount of log records being produced.

Shrinking a log file regularly is not only meaningless, but it is a bad thing to do. It's bad, because when the log file has to grow, the disk has to be zeroed out and that takes resources, and stalls the process that triggers the autogrow.

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

That's very easy to achieve: don't shrink the log, but keep it at a size where it can fit the workload.

By the way, creating and dropping tables does not produce a lot of transaction log, as that is just metadata. Writing lots of rows to these tables will of course produce a lot of transaction log. However, dropping these big tables will not, since all that needs to be logged is metadata and the extent deallocations. (But if they delete data with the DELETE statement, that again produces plenty of log records.)

Answer 2

one reason for shrinking the database log would be to reduce the vlf count. I'm finding that it can be done by shrinking the database log file gradually on the primary replica of an availability group. You MUST MAKE sure that you don't use the truncate the log with truncate only. That will kill the log info that is needed to keep the database synchronized to the secondary. ALSO, don't change the recovery model to SIMPLE, as that will kill the log info that is needed as well.

Note: The reason I wanted to shrink the log file was to reduce the VLF count for the log file, when they get large. A large VLF count in the thousands can affect performance and recovery time.

1. Run Tran log backup for each database that you want to shrink the log file.
2. run shrink command ( you may need to run this in a loop to gradually reduce the file size, I've seen some examples that do this with a wait in between shrink commands. At the end, grow the log in one command to the desired max size (don't leave it it at 100 MB, if it had grown to 2 GB on it's own, just grow it back to about to where it started in size or some reasonable size). USE [AdventureWorks]
   GO
   DBCC SHRINKFILE (N'AdventureWorks_log' , 256) <-- do this multiple times, gradually reducing the target size each time, (ie... 2000, 1800, 1600......etc. to 100)
   GO

      Use [master]
   
      GO
   
      ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_log', SIZE = 2048000KB )
   
      GO
   
   3.  Then run a script to check the VLF count when you are done, and probably run one more tran log
   
        backup.  When I shrink the tran log in this way it does not break my synchronization for the database                that's part of the availability group.
   

Answer 3

You use DBCC SHRINKFILE(logfile) as always.

But why do you want to shrink? Shrinking is a very exceptional operation.

Answer 4

Hi @Sai ,

Backup operation will do not reduce the size of database files. You need to run DBCC ShrinkFile(logfile) command to reduce the size of log file.

If you do not backup log file regularly, your log file will grow rapidly.

You can follow below steps to shrink log file.

1.Use below T-SQL to check the size of log file, and the space used for log fie.

DBCC SQLPERF(LOGSPACE)   
GO  

2.Perform transaction log backup
sometimes, we have to perform multi transaction log backup before the transaction log could be shrank.

3.Shrink transaction log

DBCC SHRINKFILE(logfile)  

If the transaction log cannot be shrank, please execute the following statement to check why the transaction log cannot be shrink:

SELECT log_reuse_wait_desc FROM sys.databases WHERE name='<database name>'  

If the result is other than NOTHING, please perform corresponding operation. Then please try step 2 and 3 again.

4... Check the size of log file again.

DBCC SQLPERF(LOGSPACE)   
GO  

Refer to the blog Handling large log files and DBCC SHRINKFILE (Transact-SQL).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 5

I have huge growth in log file(mdf- 500GB and ldf - 800GB). We are running transactional log backup for every 30 mins but I didn't see much difference from 1 week.

So why do you think that the log will stop to grow if you shrink it? I would say that it is more likely that it will start to grow again. And since it has to zero out the disk when growing, that will take resources.

On the other hand, if you have identified why the log grew and you have made sure that it will happen again, you have a valid reason to shrink the log.

Do I need to remove database from always on to run DBCC SHRINKFILE(logfile) command or can I run normally?

I would expect so, but I am not sure. But if you have to take it, you need to ask yourself, why you did but the database in an AG in the first place. The normal reason would be that you want a HA solution. But taking the database out of the AG will mean downtime - exactly what you want to avoid if you invest in HA.

So if you have to take the database out of the AG to shrink the log, that's one more reason not to do it.