We're running Azure SQL Single Database (Serverless tier) and are having problems with our development environment SQL servers appearing not to pause despite the DBs being out of use and autopause being correctly configured.
We've narrowed it down to SSMS running the following SQL query against the DB if it has a query window open but we have no idea how to prevent it.
(@type int)SELECT file_id, name, size AS size_8KB, max_size AS max_size_8KB, ISNULL(FILEPROPERTY(name, 'SpaceUsed'), size) AS space_used_8KB
FROM sys.database_files
WHERE type = @type ORDER BY size DESC
This query is run every 5 - 7 minutes while SSMS is open. This is causing us considerable headache and cost.
Does anyone know what feature of SSMS is calling this query and how to turn it off?
As I know about the serverless, when the database is inactive, it can be paused. But when the SSMS or query editor opened, the connection to SQL database is open which means the database is always active., then the autopause congifuration won't work.
Ref this document: https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview#performance-configuration
HTH.