Long Running Query - Azure SQL server

abdulaziz 86 Reputation points
2023-02-25T08:00:48.2366667+00:00
(@backupTypeEquals nvarchar(1),@backupPathLike nvarchar(229))SELECT TOP 1  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm]  FROM [0254e7e5-6388-43b8-8865-b09767c7ff3c].[sys].[backup_metadata_store] WHERE (backup_type = @backupTypeEquals) AND (backup_path LIKE '%' + @backupPathLike + '%') ORDER BY backup_start_date DESC

Above query is taking longer time on every hour. It is not related to our app. 

Will it cost us?
Why this is happening every hour?
Azure SQL Backup is 12/24 and 1 week no every hour.
how we can get rid of this?


Azure SQL Database
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2023-02-25T09:27:30.5133333+00:00

    Hi,

    Will it cost us?

    Depend on the Service tier, the storage and more.

    Why this is happening every hour?

    You should monitor and check the setting of your backup including the long-term backup retention. Maybe you changes something

    https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure?view=azuresql&tabs=portal&WT.mc_id=DP-MVP-5001699

    Azure SQL Backup is 12/24 and 1 week no every hour.

    As I mentioned, you check since the default can be change. In addition what you wrote is not accurate. In addition to these, by default there is a Transaction log backups approximately every 10 minutes.

    https://learn.microsoft.com/azure/azure-sql/database/automated-backups-overview?view=azuresql&WT.mc_id=DP-MVP-5001699#backup-frequency

    User's image

    taking longer time on every hour.

    If you mean that every hour it take longer than previous hour then this is not normal and you should monitor the size of the transaction log file and the data file and the activity during the backup. You can use Extended Event to monitor activity and simple queries to get the size of the files.

    More information if you can share might point us a bit better but the above is the general answer. I hope this help you to solve the issue.