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

Abhishek Jadhav 40 Reputation points
2024-11-14T13:12:26.95+00:00

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?

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. Vijayalaxmi Kattimani 495 Reputation points Microsoft Vendor
    2024-11-14T15:29:39.5766667+00:00

    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:

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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.