Azure sql serverless database status showing pause state on azure portal.

Question

i can connect to my Azure SQL Database through SSMS or Azure Data Studio and execute queries while the Azure Portal still shows the database in a "paused" state but queries are executing through SSMS or Azure Data Studio also through my web application it shows pause state on azure portal

but when i run below command manually in azure shell it will come to online state:-

"Set-AzSqlDatabase -ResourceGroupName rg-thre -ServerName sql-rod -DatabaseName sdb-th"

this issue occur frequently when sometimes my database went to pause state then it will not shows online state

i need to run this command Set-AzSqlDatabase -ResourceGroupName rg-thre -ServerName sql-rod -DatabaseName sdb-th then it shows online

my concern is, if i am using azure sql serverless database then why this issue is occur once in a week or once in a month? is there any solution?

Answer 1

Hi @Abhishek Jadhav,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, This behaviour in Azure SQL Serverless, where the portal shows the database as "paused" even though it's accessible, could be due to how serverless databases handle idle state and auto-resume. Azure SQL Serverless databases are designed to pause during inactive periods to save costs, but there can be occasional delays in reflecting the online state on the portal, especially if auto-resume behaviour is not fully triggered by low-frequency access patterns.

Please note that, The following table summarizes distinctions between the serverless compute tier and the provisioned compute tier:

Auto-pause troubleshooting: If auto-pausing is enabled and features that block auto-pausing are not used, but a database does not auto-pause after the delay period, then application or user sessions might be preventing auto-pausing.

To see if there are any application or user sessions currently connected to the database, connect to the database using any client tool, and execute the following query:

SELECT session_id,
       host_name,
       program_name,
       client_interface_name,
       login_name,
       status,
       login_time,
       last_request_start_time,
       last_request_end_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id <> @@SPID
      AND
      (
          (
          wg.name like 'UserPrimaryGroup.DB%'
          AND
          TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID()
          )
      OR
      wg.name = 'DACGroup'
      );

After running the query, make sure to disconnect from the database. Otherwise, the open session used by the query will prevent auto-pausing.

• If the result set is nonempty, it indicates that there are sessions currently preventing auto-pausing.
• If the result set is empty, it is still possible that sessions were open, possibly for a short time, at some point earlier during the auto-pause delay period. To check for activity during the delay period, you can use Auditing for Azure SQL Database and Azure Synapse Analytics and examine audit data for the relevant period.

Note:The presence of open sessions, with or without concurrent CPU utilization in the user resource pool, is the most common reason for a serverless database to not auto-pause as expected.

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&source=recommendations&tabs=general-purpose

https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.