Post reply

Shrink log file for database in mirroring

  • October 16, 2017 at 2:40 am

    #333265

    Hello,
    I have a database that have HA of mirroring.

    i saw that the log file of this database size is about 60 GB and use only 2 percent.

    I would like to shrink the log file (It will be the first time that i'm going to shrink log file while it's in mirroring state).

    Is it ok to shrink the log file of this DB?
    What are the risk of shrink the log file of this DB?

    Thanks in advance!

  • October 16, 2017 at 2:54 am

    #1963767

    Your log will probably be 60gb for a reason. Have a look at any maintenance tasks you have scheduled particularly index rebuilds as this is pretty heavy on the log. There's no point shrinking the log only for it to need to grow again.

    Cheers

  • October 16, 2017 at 3:04 am

    #1963770

    Hi NorthernSoul,
    Thanks for your quick reply.
    I have a monitoring system (Foglight) and i can see that in the last half year the LDF file didn't grow (als othe usage of the ldf file was stable around 2 percents).
    Thus, i would like to shrink this file, but i am also want to understand what is the pros and cons for doing that on database with mirroring.
    Can you please advise?

  • October 16, 2017 at 3:22 am

    #1963773

    You should be fine to shrink the log on your principal as a one off activity, obviously it will be mirrored.

    Thanks

  • October 16, 2017 at 3:46 am

    #1963777

    89netanel - Monday, October 16, 2017 3:04 AM

    Hi NorthernSoul,
    Thanks for your quick reply.
    I have a monitoring system (Foglight) and i can see that in the last half year the LDF file didn't grow (als othe usage of the ldf file was stable around 2 percents).
    Thus, i would like to shrink this file, but i am also want to understand what is the pros and cons for doing that on database with mirroring.
    Can you please advise?

    The fact that the log file doesn't grow doesn't necessarily mean that there's space in it that it doesn't need.  What do you get if you run this query on the node on which the backups are performed?

    SELECT TOP 10
         backup_start_date
    ,    backup_size/1024/1024/1024 AS SizeinGB
    FROM msdb.dbo.backupset
    WHERE type = 'L'
    AND database_name = '<Mirrored database name>'
    ORDER BY backup_size DESC

    John

Viewing 5 posts - 1 through 4 (of 4 total)

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