commvault.com commvault.com
Solved

MS SQL Transaction Logs not expiring even though Retain Until time has passed

  • 16 May 2022
  • 9 replies
  • 1004 views
tgladson
Rank
Badge +4

I have a storage policy for MS SQL backups. The retention is 21 days, but the transaction log backups do not expire after 21 days. I run the Data Retention Forecast report and it shows those log backups are not expiring because of SQL LOG RULE. I’ve read the documentation and none of it seems to apply. I have other storage policies for SQL and this isn’t an issue. This is the only storage policy with this behavior. 

Anyone have any ideas of what to check?

icon

Best answer by Ledoesp 18 May 2022, 12:41

View original
If you have a question or comment, please create a topic

9 replies

Mike Struening RETIRED
Rank
Userlevel 7
Badge +23

@tgladson  thanks for the question!

This is answered here:

 

Short version is that transaction logs don’t prune like other jobs, but the long version is pretty long (and detailed in that thread)!

https://www.linkedin.com/in/michael-struening
tgladson
Rank
Badge +4

Thanks @Mike Struening I’m not convinced yet that this is our problem. I’m doing some research to see if that is what we are seeing across multiple storage policies. I’ll reply with my findings. 

Mike Struening RETIRED
Rank
Userlevel 7
Badge +23

@tgladson run the Data Retention Forecast and Compliance report against the media or Copies where you see these jobs./  You’ll get a clearer picture of what is keeping them around.

https://www.linkedin.com/in/michael-struening
L
Rank
Userlevel 5
Badge +13

Can you check if for this particular MSSQL storage policy do you have a selective copy defined compared with the other policies for SQL where this behaviour is not seen for the transactional logs? 

If answer is yes, then reason will be the Honor SQL Chaining for Full jobs on Selective copy parameter.

 

tgladson
Rank
Badge +4

Thanks @Ledoesp! This is very helpful! I am going to research that option.

tgladson
Rank
Badge +4

I turned off the “Honor SQL Chaining for Full jobs on Selective copy” parameter. I will monitor to see if it did the trick. 

S
Rank
Userlevel 6
Badge +14

Just adding this for reference for other folks reading/searching the thread.
https://documentation.commvault.com/11.26/expert/11019_media_management_configuration_data_aging.html

 

Honor SQL Chaining for Full jobs on Selective copy

Definition: Specifies whether or not to honor SQL chaining for full jobs on a selective copy.

Default Value: 1

Range: 0 (No) or 1 (Yes)

When the value is set to '1', full jobs on selective copy retain logs that are chained. Full backups are not aged.

When the value is set to '0', full jobs on selective copy are retained by basic retention criteria. Full backups do not retain the logs that are chained with SQL_CHAIN or SQL_LOG_RULE.

Usage: By default, the value is set to 1. Full jobs on selective copy retain logs that are chained. Full backups are not aged.

 

tgladson
Rank
Badge +4

Thanks, @Scott Reynolds! One more question: When this parameter is changed from 1 to 0, will Data Aging clean up all of the old log backups that have exceeded the original basic retention?

S
Rank
Userlevel 6
Badge +14

@tgladson 
Yes that is correct old backups will be valid for aging.

Terms & ConditionsCookie settings