1

Good afternoon,

We have 4 node SQL Server configure in HA/DR. Periodically, we purge the old data to free up the space. Recently, after the SQL Job to purge the data was executed, we notice an increase of Freespace in the transaction log file size. Please review the following image. enter image description here

Questions:

  1. How to release the Freespace without causing any disruption in HA?
  2. Do we need to perform the operation to each node in HA/DR?
  3. The auto growth is configured as follow: enter image description here
  4. It is our OLTP (busy) database. Do we need to perform any pre-requisite steps before releasing the Freespace?
  5. Does it cause any fragmentation?

Please provide your valuable input to resolve this issue. Let us know if you need any further information. We are using SQL Server 2016 Enterprise edition.

rushabh138
  • 13
  • 4

1 Answers1

0

You would need to do a shrink to release that space back to the drive. However, if this operation happens regularly, the log will just have to grow again to accommodate the process next time.

It would be a per node operation.

Fragmentation affects data files. Log files are always sequential.

GfDBA
  • 1
  • @ GrayfoxData thanks for your response. Can we DBCC SHRINKFILE (N'db_Log' , 0, TRUNCATEONLY) during normal business hours? Do need to take backup of the database or log file? – rushabh138 Dec 21 '23 at 18:55
  • @rushabh138 Typically shrinking should be avoided. How often does your SQL job that purges data runs? – J.D. Dec 22 '23 at 00:54
  • While it would be best to do it during low use times, I've done plenty of log file shrinks during business without significant impact. All this operation is doing is releasing empty space from the end of the log file.

    It is always good to have a current backup, but this shouldn't require a special backup.

     – GfDBA Dec 22 '23 at 10:49
  • Last question, do I need to run on the command on each node in AG? – rushabh138 Dec 22 '23 at 19:12
  • Yes. You would need to run it on each node. – GfDBA Dec 23 '23 at 20:06