Azure serverless SQL DB auto-pauses when it should stay online

Question

I created a serverless Azure SQL Database that I want to use for a personal app so my wife and I can manage our vegetable garden and to keep my brain busy. I selected the Free option with Overage billing, so happy to pay for any extra storage and compute at the end of each month. I will only be accessing the db once or twice a day so I don't want to have to wait for it to resume from auto-pause every time I use the app. At first I tried running Runbook jobs scheduled every half-hour that log in and update a timestamp in one of the tables, hoping that would keep the db online. It worked some of the time but often the db auto-paused anyway at the end of its 1-hour delay time. I set the delay time to 7 days but the db is still auto-pausing, sometimes in less than an hour from my last manual login. Finally, I disabled Auto-Pause altogether but it still auto-pauses after a short time, even though the Auto-Pause Delay is still set for 7 days.

The online discussions of Auto-Pause I have found are mostly the opposite of my problem - people whose db does not auto-pause when they want it to. The ones that address my problem say to disable Auto-Pause (which I have done), or schedule a job that logs on and changes something (which I have done).

Can anyone tell me what is going on and suggest a solution that will keep the db online? Thanks in advance people.

Answer 1

Hello @Peter !

Welcome to Microsoft QnA!

As we can see :

The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.

Similar Thread :

https://learn.microsoft.com/en-us/answers/questions/707576/how-do-i-pause-an-azure-sql-database-%28serverless%29

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview

There is no explicit way to pause but it pauses automatically based on some inactivity.

---

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Hi Peter •,

As suggested by Konstantinos Passadis •, Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.

Please note below:

If auto-pausing is enabled, but a database does not auto-pause after the delay period and the features which block auto-pausing are not used, then the 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:

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 see if such activity has occurred during the delay period, you can use Azure SQL Auditing and examine audit data for the relevant period.

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.

If above does not help in your scenario, I would recommend you to please file a support ticket for deeper analysis and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks

Answer 3

Thanks for all the suggestions but I have found a work-around. I created a new database, created and populated all the files, and deleted the old database. The new database I configured as Auto-Pause: Disabled right from the start. This one has now been online for over 24 hours without auto-pausing, which is what I need.

Answer 4

Hi Peter •,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others (Opens in new window or tab)", I'll repost your solution in case you'd like to "Accept (Opens in new window or tab)" the answer.

Solution taken by user:

Created a new database, created and populated all the files, and deleted the old database. The new database user configured as Auto-Pause: Disabled right from the start. This one has now been online for over 24 hours without auto-pausing.

If you have any other questions or are still running into more issues, please let me know.
Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.