question

TonyCurtis-2430 avatar image
2 Votes"
TonyCurtis-2430 asked TonyCurtis-2430 edited

Azure SQL DB Serverless won't auto pause

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?

azure-sql-database
· 2
5 |1600 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.


@TonyCurtis-2430 Thank you for reaching out.

Please check if Auto-pause delay is configured to your needs.
I have mine set to pause after 1 hour of inactivity.

20698-image.png

Do you have any Active GeoReplication configured. That will prevent the database from getting paused.

Are you using managed identity of the Virtual machine to connect to Azure SQL Database?


0 Votes 0 ·
image.png (19.4 KiB)

@TonyCurtis-2430 Just checking in to see if your issue is resolved.
@SarangPitale-6417 has posted a workaround below.
Please try and let us know if that helps.

0 Votes 0 ·
TonyCurtis-2430 avatar image
0 Votes"
TonyCurtis-2430 answered TonyCurtis-2430 edited

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.

5 |1600 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.

SarangPitale-6417 avatar image
0 Votes"
SarangPitale-6417 answered

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

5 |1600 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.