Azure SQL Single DB (Serverless) Autopause vs SSMS (SQL Server Management Studio)

Question

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?

@LeonYue Nope. No changes to anything on the SQL server or DBs. This is SSMS specifically that is doing this. — toadflakz, Jan 13 '21 at 09:42
@toadflakz It's so strange.. can you try end the SSMS in task manage? — Leon Yue, Jan 13 '21 at 09:49
@LeonYue Just closing SSMS works. The problem is developers and discipline in closing down tools. Surely this feature is something that should be configurable ito turning it off? — toadflakz, Jan 13 '21 at 11:59
@toadflakz 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. See here:https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview#performance-configuration — Leon Yue, Jan 13 '21 at 14:54
It seams we can't turn it off( don't know how and where to turn it off). — Leon Yue, Jan 14 '21 at 02:37
Hi @toadflakz, hope you're doing well! May I post this as answer? — Leon Yue, Jan 15 '21 at 00:04
What's interesting about this is that according to another doc page at https://azure.microsoft.com/en-us/blog/connections-and-sql-azure/ , if your SSMS connection is idle for 30 minutes, Azure SQL will close it. What that suggests to me is that they've tested that this works, because they specifically call out SSMS, so I'm wondering if there is indeed a setting that will prevent the query in the question from running periodically? — WaitingForGuacamole, Mar 10 '21 at 16:06
Interesting, in SSMS -> Tools -> Options, there's a setting called "SQL Server Always On", and there's a "Turn on automatic refresh" option with refresh interval that's set (on my machine) to 30 seconds? — WaitingForGuacamole, Mar 10 '21 at 16:13
Same for me but the query really only shows up every 5 mins in azure query insights. This is some crazy behavior imho. — Triamus, Jun 07 '21 at 12:52

score 2 · Accepted Answer · answered Jan 18 '21 at 13:45

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.

Azure SQL Single DB (Serverless) Autopause vs SSMS (SQL Server Management Studio)

1 Answers1