question

ITAdmin-3938 avatar image
0 Votes"
ITAdmin-3938 asked AlbertoMorillo edited

AZURE SQL Serveless Pause and resume is not working

Since beginning of MAY, my Azure SQL Pause is not working.
It seems something wrong, and the cost is doubled because of the issue.

What should I do on this case?

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

I have been dealing with this for months with a serverless database I managed that is intended for QA/Testing.

If the database uses Full-Text search you will see the following query continuously running and avoiding the auto-pause.

 SELECT c.*,
 i.object_id, i.unique_index_id, i.is_enabled, i.change_tracking_state_desc, i.has_crawl_completed,
 i.crawl_type_desc, i.crawl_start_date, crawl_end_date,
 i.incremental_timestamp, i.stoplist_id, i.data_space_id, i.property_list_id,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFullTextMergeStatus') as int) as merge_status,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextDocsProcessed') as int) as docs_processed,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextFailCount') as int) as fail_count,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextItemCount') as int) as item_count,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextKeyColumn') as int) as key_column,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPendingChanges') as int) as pending_changes,
 cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPopulateStatus') as int) as populate_status
 FROM [57933ff2-c6f3-410d-a6ff-fa5204000fc3].sys.dm_fts_active_catalogs c
 JOIN [57933ff2-c6f3-410d-a6ff-fa5204000fc3].sys.fulltext_indexes i on c.catalog_id = i.fulltext_catalog_id

Please verify if Query Data Store or automatic tuning is turned off? They could trigger auto-resume. Please turn off the following features also as they can trigger auto-resume: threat detection, data discovery, auditing, data masking, Transparent data encryption, SQL Data Sync. Disconnect from SSMS.

I found many developers were leaving their SQL Server Management Studio sessions connected to the database. I scheduled a scale up/down of the tier where I only change the auto-pause interval, to periodically kill any connections left by developers.

Today I received the following notice from Azure, which means the next monthly bill could be higher. I hope you find quickly the culprit.

96699-serverless.png



serverless.png (29.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

learn2skills avatar image
0 Votes"
learn2skills answered learn2skills edited

Hi @ITAdmin-3938

Thank you for posting in Q & A.
Refer to the below url Database Resume it is already answered.
https://stackoverflow.com/questions/64345506/resume-serverless-azure-database


If the Answer is helpful, please click Accept Answer and up-vote, this can be beneficial to other community members.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered

Would suggest to raise a support ticket if you have support plan so that Microsoft team can take a look at it from the backend. You can even request for a refund.

Main intention of Serverless is to save the cost and pause must work.


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.