Auto-pause not turning on for serverless database

Question

I have a serverless database in which the Auto-pause delay is not turning on, I have selected 1 hour and 2 hours and when the update completes it shows 0 days, 0 hours, 0 minutes. What cold be the reason for this? It is set as "General Purpose (most budget friendly, serverless computer), Standard Series (Gen5), Max Vcores 80, min vCores 10.

Luis

Answer 1

Thank you all, I switched to "Provisioned" and back again to "Serverless" and it let me turn on the Auto-Pause feature. Go figure ¯_(ツ)_/¯

Answer 2

Hi Luis,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your question; I'll be more than glad to help you out.

I am not sure what your problem is... "Auto-pause is not turning on"... your database is not pausing, or won't your database come online after it paused?

Maybe this guide helps you a bit to find the cause!

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

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

Please verify there are applications sessions or user sessions preventing the auto-pause:

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'
      );

Applications connected to the database like SQL Server Managment Studio (SSMS) can prevent the database to auto-pause and can wake up the database using Object Explorer on SSMS.

Other features like Full-Text Search in use on the database can prevent also the database to go to auto-pause.

You can use Query Performance Insights to see the query executed to the database, including things like backups (exports) that can prevent the database to auto-pause also.

Finally these Azure SQL or Azure features can prevent auto-pause also:

• Threat Detection
• Azure SQL Auditing
• TDE (Transparent Data Encryption)
• Vulnerability Assessment
• Data Masking
• Data Discovery / Data Classification
• Auto-Tuning
• Database Copying
• SQL Data Sync