Azure SQL DB Serverless won't auto pause

Question

I am trying to understand why my Azure SQL Database will not autopause.

When I look at the "compute utilization" graph showing CPU, Data IO and Log IO percentages, they are flat while the "App CPU billed" chart shows vcore seconds being billed and the status shows "online".

Digging deeper I added some metrics to the compute utilization graph: Failed connections, successful connections and sessions percentage. I found that some services were connecting to the database from the only vm that knows anything about the db, so I reconfigured them to shut off until morning.

All during the night I continued to get "App CPU billed" with the DB online even though the CPU, Data IO, Log IO, Failed connections, Successful connections and sessions percentage were all at zero. No auto pause.

The only way I can get the DB to auto pause is to turn off the VM or the two services that connect to the databases on that VM, but as I stated above there are no connections showing for the entire evening so I am at a loss as to what is keeping the DB from auto pausing.

According to docs:

Autopausing is triggered if all of the following conditions are true for the duration of the autopause delay:

>

Number sessions = 0
CPU = 0 for user workload running in the user pool

How can I determine why the DB is not auto pausing? What additional metrics can I track to know what activity is triggering activity on the DB?

Answer 1

What I finally did was actually turn off the service, rather than using the service's settings to keep it quiet. When I used the services "active time" scheduling configuration it didn't actually turn off the service completely. Using the Query Performance Insight menu option and digging into the actual queries being run (including the actual query text) revealed the service was still querying the DB even though I had configured it to go to "sleep" using the service's own scheduler. Query performance insight was extremely helpful in discovering what was going on there.

I was able to schedule the services in question to actually shut down using task scheduler which allowed the DB to pause.

A word of warning though. I later found that navigating to and running queries in the Query Performance Insights tab actually woke up a paused database, so you want to be judicious when using that tool.

Answer 2

@Tony We have the same issue. Auto pause is enabled with a 1 hour delay and GeoReplication isn't configured. The DB is connected to Azure Data Factory and nowhere else except occasionally being connected from SSMS. The ADF pipelines runs just few hours a day yet the DB doesn't pause. A workaround is to change the pricing tier of the DB to DTU based model and then again change it to serverless, then it starts working.

Answer 3

What option should you select to change the auto-pause setting of a serverless SQL Database?

The auto-pause setting can't be changed, and it's fixed at one hour

Ensure that there are no active transactions in the database, as the auto-pause change will result in a disconnection

The Azure portal allows you to change the auto-pause setting at any time