Managed backups transaction log frequency is not honored

Question

Hello,

Configuring managed backups schedule at 5 minutes intervals for transaction log backup is not honored. The feature backup the log at 10 minutes intervals instead of 5.

I've attached a screenshot with an example.

"managedbackup.fnbackupdbconfig" shows it is configured at 5 minutes intervals but when querying msdb for backup history, the log are being taken at 10 minutes intervals.

I've also restored one of the affected database to make sure that the log chain was not somehow broken and confirms that nothing else occurred between two log backups.

No error in the SQL Agent logs.
No error in the SQL management logs.

I've also tried the 00:00 configuration but like the documentation states it stops taking log backup which it did. Re-configuring the 5 minutes intervals afterward didn't help in any way.

This behavior occurs on SQL Server 2017 CU22 and on SQL Server 2016 SP2+ as well (they are stand alone instances per VM).

Databases use the proper compatibility level (140 or 130 depending on which instance is tested)
Recovery model: Full (of course) and it didn't change during any of the tests.

Database size is not a factor because it happens to extremely small one (less than 100 megs) up to some heavier ones with more than 100 gigs.

Regards,

Answer 1

Hi LucAndreJolivet-3502,

Could you please try to use managed_backup.sp_backup_config_schedule with @Loughborough _backup_freq to specify frequency of transaction log backups again?
Please refer to managed_backup.sp_backup_config_schedule which might help.

Best Regards,
Amelia

Answer 2

Hello AmeliaGu-msft,

Thanks for taking time to help out.

Could you please try to use managed_backup.sp_backup_config_schedule with @Loughborough _backup_freq to specify frequency of transaction log backups again?
I've already did it in my initial attempt (reconfiguring) but as requested, I reproduced the issue with new screenshots.

I've configured managed backup to initially take log at 15 minutes frequency (which it did correctly) than I switch to 5 minutes frequency and the log were then being taken at 10 minutes frequency. I also point out where SQL shows the configured interval to demonstrate it was indeed configured at 5 minutes frequency.

Here's the command I used to setup managed backups schedule for 5 minutes frequency:

EXEC msdb.managed_backup.sp_backup_config_schedule
@DBase _name = '[DBName]'
, @scheduling_option = 'Custom'
, @full_backup_freq_type = 'Daily'
, @Chuck _begin_time = '01:00'
, @Chuck _duration = '02:00'
, @Loughborough _backup_freq = '00:05'

Please refer to managed_backup.sp_backup_config_schedule which might help.

yes I've already read the documentation and to show you I did, I will pinpoint you a misleading information with the way managed backup schedule works.
The documentation says it can be configured at the minute, but when the command is run, SQL prompt an error telling 5 minutes is the lowest possible frequency and needs to be configured using a multiple of 5 minutes.

This determines the frequency of transaction log backups. These backups happen at regular intervals rather than on the schedule specified for the database backups. @Loughborough _backup_freq can be in minutes or hours and 0:00 is valid, which indicates no log backups. Disabling log backups would only be appropriate for databases with a simple recovery model*

Regards,

Answer 3

Hi LucAndreJolivet-3502,

Thanks for your reply.
It seems the Log Backup frequency can be set to a multiples of 5 minutes but it cannot be implemented at 5, even if you specify 5 for @Loughborough _backup_freq.
I recommend you submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server.
Sorry for the inconvenience and thank you for your understanding and patience.

Best Regards,
Amelia

Answer 4

Hello AmeliaGu-msft,

Thank you for your time.
I had already done so end of January (more than a month ago) and still no answer. Hence why I posted here hoping to get something more.
This issue is relatively important for us considering the very high load we need to manage.

42563632-managed-backups-transaction-log-frequency-is-not-h

Regards,

Answer 5

The feedback site is not a place to get support. The purpose of the feedback site is to inform Microsoft about an issue you have identified, but for which you need to a fix right now. Microsoft gets a lot of bug reports and enhancement suggestions this way, and issues that keep reoccurring or that get many votes are more likely to be addressed. But it can take quite a while before that happens. I report issues on the feedback site myself every once in a while, but I rarely hear anything back.

This site is a kind of support forum, but only a light one. Here you can help with something you have not set up correctly, or get advice how to proceed.

I have never worked with Managed Backups myself, so I can't test myself.

Since you want this fixed, I think the best in your case is to open a support case. I should warn you that this can cost you an arm and a leg, if they deem that the current behaviour is by design. On the other hand, if the conclusion that is that this is a bug, you will be refunded what you are charged initially.