Wanted: Help diagnosing auto-pause problem for Minimal ASP.NET/Blazor Serverless Azure SQL Demo

Question

I have a minimal blazor (server) application that read/writes to a serverless Azure SQL: deploy-SqlSvr.bicep and master_hqdqhengdtz4w-website.yml and Index.razor.

Basically there is just me doing a few inserts from my sample blazor app to confirm it is working and then let it sit.

As suggested here: how-to-configure-tiny-and-cheap-azure-sql.html I deployed and ran this query: serverless-tier-overview and the query came back empty...

So how can I determine if I am pausing? At this point (several months ago) I let it sit over the weekend and azure charged me $10/day...

Now that I am running it again, how do I determine if it is paused or going to pause (besides looking at my bill 3 days later).

According to my bicep code, I have auto pause explicitly set to the default value of 60 (I think this is minutes).

It says it won't pause if I have an open session...

Do I have an open session? I'm not sure what an open session is but I have my SQL insert command triggered by an HTML button and, as you can see from my C# code in my Index.razor page, I dispose of my connection... I think this means I do not have an open session...

Now if I left the Azure SQL Query editor going (the web page in the portal.azure.com), would that result in an open session?

I've never set up an audit before. Would that help me? What do I audit? The server? the database? What would I look for in the audit?

Thank you

Siegfried

Tue Oct 04, 2022 Evening update:

So like I said above (on Monday), I ran that query and got no results and deleted the deployment.

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

I deployed again this afternoon (Tuesday). I just ran that query again and got these results:

53  
f115b12ee434  
Core Microsoft SqlClient Data Provider  
Core Microsoft SqlClient Data Pr  
b3a8901a-4687-4295-82c1-3cd2f49d0f6e@7a838aec-0b9e-4856-a3b5-2b02613f36a2  
sleeping  
2022-10-05T01:26:56.8100000  
2022-10-05T01:26:57.3570000  
2022-10-05T01:26:57.3570000  
Query succeeded | 0s  

What does this mean? Was it sleeping and I just woke it up with this query?

Wed Oct 05 2022 Morning Update (PDT):

Darn, this web site did not save my latest update!

This morning I ran the above query twice from the portal.azure.com and the first time it came back and said that the server was unavailable which would make sense if it was sleeping. I don't have the exact response because this website lost my work...

Since the whole deployment was running overnight, I went to my website and pressed the button to insert a new record into the Azure SQL database it worked immediate (I was expecting it to take 30 seconds to wake up).

Now the second time I run the above query I get this:

 78  
dc989dddda53  
Core Microsoft SqlClient Data Provider  
Core Microsoft SqlClient Data Pr  
b3a8901a-4687-4295-82c1-3cd2f49d0f6e@7a838aec-0b9e-4856-a3b5-2b02613f36a2  
sleeping  
2022-10-05T16:34:43.9600000  
2022-10-05T16:34:44.5270000  
2022-10-05T16:34:44.5300000  
Query succeeded | 0s  

What does this mean? Is the server working properly now and going to sleep and then waking up when the web site needs it?

I'm not sure I have time to deploy that audit feature today... Would the audit feature confirm my suspicions?

Thu Oct 06 2022 Morning update:

The database has been idle since yesterday morning; at that time I had excecuted just a few trivial INSERT and SELECT commands via my web site just to prove it was working.

I set up the audit feature last night and here is what I see this morning:

What does this mean? Does it mean the server is functioning properly and sleeping well?

I looked at my azure cost analysis: for a completely idle server it increased about $1 since yesterday and we are up to $4.24 in total for two days. According to the pricing calculator I'm anticipating $5.31 a month for a serverless Azure SQL (with very nearly no data).

I ran the above query again and this time received

Query succeeded: Affected rows: 0  

What does this mean? Is it sleeping?

So I go to my web site and execute another trivial SQL INSERT and SELECT and run the query again:

I see I have some more entries in the audit now:

OK, it looks like it was sleeping and the Blazor App/web site/server/ woke it up... So now my cost forecast is about $30/mo which is 6 times what the pricing calculator says...

I'm using the free web server and I'm consuming a few pennies a day on some blob storage so very nearly all my charges are for this serverless Azure SQL.

How do I configure this to be approximately $5/mo like the pricing calculator says?

Thanks
Siegfried

Fri Oct 07 2022 Morning Update:

Darn, I wish I kept the screen shot when I first deployed the SQL server. It was about $3 after deployment and two or three trivial queries. It jumped about $1.50 between Wed and Thu. I wonder if this could be due to installing the audit feature?

Here is yesterday's (Thu Oct 06):

Here is today (Fri Oct 07) at 4.90... That is not so bad, only $0.13 increase for one day of complete idleness. But the forecast is still well over $5.31/mo.

Sat Oct 08 2022 Evening Update

So much for my fantasies about $0.13/day! What happened! I did not touch the database for a second day in a row and suddenly another $2.37! We are now definitely over the $5.31 advertised cost.

Sun Oct 09 2022 Evening Update

No change since yesterday... Holding at $7.14.

Sat Oct 15 2022 morning update:

Thu I had a screen share with Microsoft support and we disabled the auditing and we are still up $0.45. Here is the latest:

$4.77 2022-10-06 124759
$4.90 2022-10-07 061432
$7.14 2022-10-07 061432
$7.14 2022-10-09 184018
$7.14 2022-10-10 132328
$7.66 2022-10-11 082656
$8.34 2022-10-11 195529
$8.34 2022-10-12 070448
$8.34 2022-10-12 181927
$8.87 2022-10-13 093002
$9.32 2022-10-14 064032
$9.39 2022-10-15 090148