question

LucAndreJolivet-3502 avatar image
0 Votes"
LucAndreJolivet-3502 asked ·

Managed backups transaction log frequency is not honored

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,

73435-managedbackuplogbackupfrequencynothonored.jpg


sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

Hi LucAndreJolivet-3502,

Could you please try to use managed_backup.sp_backup_config_schedule with @log_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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LucAndreJolivet-3502 avatar image
0 Votes"
LucAndreJolivet-3502 answered ·

Hello AmeliaGu-msft,

Thanks for taking time to help out.

Could you please try to use managed_backup.sp_backup_config_schedule with @log_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.

74521-managedbackup15minuteslogfrequency.jpg


74531-managedbackup5minuteslogfrequencynothonouredv2.jpg



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

EXEC msdb.managed_backup.sp_backup_config_schedule
@database_name = '[DBName]'
, @scheduling_option = 'Custom'
, @full_backup_freq_type = 'Daily'
, @backup_begin_time = '01:00'
, @backup_duration = '02:00'
, @log_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. @log_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

73841-documentationmisleadingwithmanagedbackupconfigurat.jpg


Regards,


[3]: /answers/storage/attachments/73756-managedbackuplogbackup5minutesinterval.jpg











·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

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 @log_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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LucAndreJolivet-3502 avatar image
0 Votes"
LucAndreJolivet-3502 answered ·

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,

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.