Serverless Azure SQL Database Not Auto Pausing

Question

I have reconfigured a production database from Azure's DTU pricing to the VCore serverless pricing. My thinking is that once paused time was taken into account, I would be able to beef up the resources for the active time and therefore get better performance for the same cost.

However, when I look at the database metrics, it isn't pausing. I have checked the auto tuning options and the only one that is switched on is the FORCE PLAN option, which is inherited from the server. I don't think it's this causing the database to not pause, however, as I made a copy of the database onto a different server (which also has inherited the FORCE PLAN auto tuning option) and that one does auto pause as expected.

I then had a look in the Query Performance Insight section on Azure Portal to see what commands were being run at those times and it is all non-user queries like the following:

SELECT file_id, name, type, data_space_id, state, size, max_size, physical_name FROM sys.database_files

SELECT 100 * Sum(modified_extent_page_count) / Sum(total_page_count) AS ModifiedPagePercentage, 
                    Sum(modified_extent_page_count) as SumModifiedExtentPageCount
                FROM sys.dm_db_file_space_usage

SELECT  [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] ...

Other things I have checked:

• No long term backup retention policies, other than the usual ones that are included such as PITR
• No replications of database

The strangest thing seems to be how a copy of the database sitting on a different server doesn't exhibit the same behaviour, and pauses as expected. This makes me think there is a server setting switched on somewhere that is causing it.

Has anyone else experienced this?

Answer 1

I have a doubt about how the auto-pause delay is set as you know he default is 1 hour, but you can configure it to be as short as 1 hour or as long as 7 days.

Sometimes, internal Azure services or monitoring tools might be hitting the database, preventing it from pausing and this is what happened with me once since I had to check the firewall rules that might be allowing such traffic.

In an another time, I discovered that there was an active connections keeping the database awake.

The queries you mentioned are typically internal queries however, if they are running frequently, they might be preventing the database from pausing. Investigate if these queries are being run more frequently than expected.

As a temporary workaround, you can manually pause the database when you know it won't be in use. This isn't ideal but can help mitigate costs until the issue is resolved.

Query to check active connections :

SELECT 

    session_id, 

    login_name, 

    host_name, 

    program_name, 

    status, 

    last_request_start_time, 

    last_request_end_time 

FROM 

    sys.dm_exec_sessions 

WHERE 

    is_user_process = 1;

Query to check the database scoped config :

SELECT * FROM sys.database_scoped_configurations;