Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (2023)

asked 2021-11-18T15:03:30.753+00:00 by

Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (1)

Sai 126Reputation points

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.

SQL Server

SQL Server

A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.

8,431 questions

0No comments

0{count} votes

    Sign in to comment

    7 answers

    Sort by: Most helpful

    Most helpful Newest Oldest

    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.)

      0No comments

        Sign in to comment

      1. answered 2023-01-17T20:32:40.6433333+00:00 by

        Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (3)

        Ted Henderson 5Reputation points

        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 ) GO3. 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.

        0No comments

          Sign in to comment

        1. answered 2021-11-18T22:57:22.087+00:00 by

          Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (4)

          Erland Sommarskog 67,056Reputation points Microsoft MVP

          You use DBCC SHRINKFILE(logfile) as always.

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

          1. Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (5)

            Sai 126Reputation points

            2021-11-19T09:29:31.017+00:00

            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.

          Sign in to comment

        2. answered 2021-11-19T01:31:37.453+00:00 by

          Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (6)

          CathyJi-MSFT 20,656Reputation points Microsoft Employee

          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".

          1. Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (7)

            Sai 126Reputation points

            2021-11-19T09:27:35.477+00:00

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

          Sign in to comment

        3. answered 2021-11-19T22:03:10.54+00:00 by

          Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (8)

          Erland Sommarskog 67,056Reputation points Microsoft MVP

          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.

          1. Shrink a database log file which is synchronized (Always on Group) - Microsoft Q&A (9)

            Sai 126Reputation points

            2021-11-21T07:15:56.383+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.

          Sign in to comment

        Sign in to answer

        Top Articles
        Latest Posts
        Article information

        Author: Kerri Lueilwitz

        Last Updated: 11/10/2022

        Views: 6227

        Rating: 4.7 / 5 (47 voted)

        Reviews: 94% of readers found this page helpful

        Author information

        Name: Kerri Lueilwitz

        Birthday: 1992-10-31

        Address: Suite 878 3699 Chantelle Roads, Colebury, NC 68599

        Phone: +6111989609516

        Job: Chief Farming Manager

        Hobby: Mycology, Stone skipping, Dowsing, Whittling, Taxidermy, Sand art, Roller skating

        Introduction: My name is Kerri Lueilwitz, I am a courageous, gentle, quaint, thankful, outstanding, brave, vast person who loves writing and wants to share my knowledge and understanding with you.