Space is not reducing after running log shrinking job

Question

Space is not reducing after running log shrinking job

Muhammad Faisal Sharif 41

Scenario:

There is SQL FCI Instance that is configure with SQL Always-On AOG which host replicas. The log shrink job executes successfully but space is not reduced instead when we run log shrink job after suspend replica seeding then it works.

Is it default behavior or do I need to change some parameters?

Any suggestion will be appreciated.

Shashank Singh 6,251 Reputation points

2020-11-04T07:50:04.967+00:00

What is output of select log_reuse_wait_desc from sys.databases where name='db_name`'. db_name is the name of db participating in AG whose log is not shrinking
Muhammad Faisal Sharif 41 Reputation points

2020-11-04T10:25:08.55+00:00

there's no output or error message, job run successfully but not reducing space instead when I do suspend replica hosted on AOG node then it works...

Scnario:

Primary: Secondary:

[FCI-N1] [FCI-N2] -(sqlfci instance) -----><------ (listener: AOGL1)---[AOG N1]
Shashank Singh 6,251 Reputation points

2020-11-04T10:35:29.223+00:00

anonymous user I am asking you to post the output of select log_reuse_wait_desc from sys.databases where name='db_name'
Jeffrey Williams 1,896 Reputation points

2020-11-04T22:52:06.297+00:00

If this is a scheduled job to shrink the log file - then you need to reconsider that operation. It is rather useless to shrink a log file - have it grow - then shrink it...just to have it grow again.

If this is a one-time operation, then it doesn't need to be a job and you need to perform the shrink in multiple steps:

1) Shrink the log file
2) Perform a transaction log backup
3) Repeat

The first shrink will shrink the file to the last used VLF - the backup should mark the last VLF as reusable, moving the active VLF to the beginning of the file. The second shrink can then shrink the file to the expected size. Note: if there are active transactions waiting to be committed on the secondary then you cannot shrink the file because those transaction will not be marked until they have been committed on the secondary.

3 answers

Your answer

Shashank Singh 6,251 Reputation points

2020-11-04T07:50:04.967+00:00

What is output of select log_reuse_wait_desc from sys.databases where name='db_name`'. db_name is the name of db participating in AG whose log is not shrinking
Muhammad Faisal Sharif 41 Reputation points

2020-11-04T10:25:08.55+00:00

there's no output or error message, job run successfully but not reducing space instead when I do suspend replica hosted on AOG node then it works...

Scnario:

Primary: Secondary:

[FCI-N1] [FCI-N2] -(sqlfci instance) -----><------ (listener: AOGL1)---[AOG N1]
Shashank Singh 6,251 Reputation points

2020-11-04T10:35:29.223+00:00

anonymous user I am asking you to post the output of select log_reuse_wait_desc from sys.databases where name='db_name'
Jeffrey Williams 1,896 Reputation points

2020-11-04T22:52:06.297+00:00

If this is a scheduled job to shrink the log file - then you need to reconsider that operation. It is rather useless to shrink a log file - have it grow - then shrink it...just to have it grow again.

If this is a one-time operation, then it doesn't need to be a job and you need to perform the shrink in multiple steps:

1) Shrink the log file
2) Perform a transaction log backup
3) Repeat

The first shrink will shrink the file to the last used VLF - the backup should mark the last VLF as reusable, moving the active VLF to the beginning of the file. The second shrink can then shrink the file to the expected size. Note: if there are active transactions waiting to be committed on the secondary then you cannot shrink the file because those transaction will not be marked until they have been committed on the secondary.

Answer 1

m 4,276

Hi anonymous user,

Is it default behavior or do I need to change some parameters?

You need to firstly backup the log and then run DBCC SHRINKFILE, steps as next:
1.backup file

BACKUP LOG DatabaseName TO DISK=’ ’

2.DBCC SHRINKFILE:

DBCC SHRINKFILE (DatabaseName_Log, EMPTYFILE)

Quote from this doc.:
...
The shrink operation can't reduce the file size any further if there's insufficient free space available.

Typically it's the log file that appears not to shrink. This non-shrinking is usually the result of a log file that hasn't been truncated. To truncate the log, you can set the database recovery model to SIMPLE, or back up the log and then run the DBCC SHRINKFILE operation again.
...

More information: shrinking-sql-log-files-in-an-availability-group-cluster-or-database-mirror

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Muhammad Faisal Sharif 41 Reputation points

2020-11-04T10:21:23.54+00:00

thanks for your suggestion, is it in context of scenario i've described above?
Shashank Singh 6,251 Reputation points

2020-11-04T10:36:42.957+00:00

The above answer is ONLY correct if your log truncation is waiting on log backup, if it is waiting on active_transaction or replica then above answer is useless. Please post output of my query

Answer 2

tibor_karaszi@hotmail.com 4,316

There are several aspects regarding shrinking the transaction log. I've written about it here: https://karaszi.com/large-transaction-log-file

Muhammad Faisal Sharif 41 Reputation points

2020-11-04T10:21:55.61+00:00

I appreciate it, let me go through your article then i'll update...

Answer 3

m 4,276

Hi anonymous user,

Is the issue solved?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Share via

Space is not reducing after running log shrinking job

3 answers

Your answer