Azure SQL Serverless tier never shuts down

Question

I'm experimenting with the Serverless tier in Azure SQL, but the database never "sleeps" because it seems that Azure is continually querying the database behind the scenes. I created the database via and ARM template.

The "Compute utilisation" and "App CPU Billed" charts on the database overview show that the database is being hit regularly (even though I am not making any queries myself). The Top queries in "Query Performance Insight" are:

(@ip_address_value bigint,@start_ip varchar(45))SELECT top 1 @start_ip = start_ip_address_value FROM sys.database_firewall_rules_table WHERE @ip_address_value BETWEEN start_ip_address_value AND end_ip_address_value OPTION (MAXDOP 1)

and

(@ip_address_value bigint,@start_ip varchar(45))SELECT top 1 @start_ip = start_ip_address_value FROM sys.database_firewall_rules_table WHERE @ip_address_value BETWEEN start_ip_address_value AND end_ip_address_value AND start_ip_address_value > 0 OPTION (MAXDOP 1)

Which seem to be from the firewall. Do I have to disable the firewall or delete all the rules to allow the database to "sleep"?

Cigarette

Could you please verify is Query Data Store or automatic tuning is turned off? They could trigger auto-resume. – Stygian

Please turn off the following features also as they can trigger auto-resume: threat detection, data idcovery, auditing, data masking, Transparent data encryption, SQL Data Sync. Disconnect from SSMS. – Stygian

learn.microsoft.com/en-us/azure/sql-database/… – Stygian

Transparent data encryption is on - everything else is off (AD admin, Advanced Data Security, Automatic tuning, auditing, failover groups). I don't have the option to turn TDE off in the portal it seems. – Cigarette

Also, I've turned off Query store with : "ALTER DATABASE [MyDatabaseName] SET QUERY_STORE = OFF;" (using the Azure portal query editor - I've not connected with SSMS) – Cigarette

Based on the documentation TDE triggers auto-resume of the database. That should be the last thing to test. – Stygian

I've turned off TDE with "ALTER DATABASE MyDatabaseName SET ENCRYPTION OFF" but something is still hitting the database. I can't see the queries though now as the query store is off. It's not at a completely regular interval though; e.g. 7:59AM, 8:09AM, 8:24AM, 8:31AM. The fact that it is the firewall rules table makes me think it has to be something in the server firewall that is doing this – Cigarette

I've created a server and database through the portal (empty) and that works, so I exported the ARM template from both and compared. The only differences in the non-working database are several items (all disabled) of type "Microsoft.Sql/servers/databases/advisors". These are DbParameterization, CreateIndex, DefragmentIndex, DropIndex and ForceLastGoodPlan. – Cigarette

Querying the connections to the database from the Portal, I get these (query editor I know, but the others I don't). Program_Name: MetricsDownloader ( DB31\WF-SpBYuHd0Mj5SoBX ),TdService ( NT AUTHORITY\SYSTEM ), BackupService ( DB31\WF-SpBYuHd0Mj5SoBX ), NodeAgent ( NT AUTHORITY\SYSTEM), NodeAgent ( NT AUTHORITY\SYSTEM), Azure SQL Query Editor, DmvCollector ( DB31\WF-SpBYuHd0Mj5SoBX) – Cigarette

I've added a new empty database under the same server and that works - TDE and query store on – Cigarette

It seems the Query Store is still enabled/running that Azure SQL Query Editor, DmvCollector ( DB31\WF-SpBYuHd0Mj5SoBX) seems related to Query Store. I would love to see if exporting the database and import it to a new one works. – Stygian

@Cigarette Could you please post your solution here? I am experiencing exactly the same issue. I have 3 serverless databases on the same SQL server and 2 of them are not shutting down, the last one does... I can not figure out why – Swampy

The solution was that the app was calling the database on a schedule that I didn't know about. The queries didn't show in the query analyzer though, so it looked like it was the firewall rules. When I refactored to completely eliminate calls from the application the database was able to shut down – Cigarette

@Cigarette - would you like to add this comment as an answer? – Nephelometer

I am experiencing exactly the same issue. My app is not performing any query. I can not figure out what is resuming the database – Alvertaalves

Answer 1

I would like to post my case here, my app was not calling the database and I still seeing those calls as well, I figured out that it was the database backup. I could see the queries in: Azure >> YourDatabase >> Left Menu, search for Query Performance Insights >> You will see the calls that was done to your database.

Image showing the Menu and database query

Bes

Answer 2

If auto-pausing is enabled, but a database does not auto-pause after the delay period, the application or user sessions may be preventing auto-pausing.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/stop-azure-sql-database/ba-p/3890369

In my case it appears that SSMS being connected to the database prevented the database from Auto Pausing.

Annieannihilate