How to check last time an Azure SQL Database went on Auto-Pause and for how long?

Question

I'm testing Azure SQL Database with the Serverless feature.

I would like to understand if the SQL Database went into Auto-Pause mode yesterday and for how long.

I asked to ChatGPT to give me a hint about what code I can use and it suggested PowerShell:

az sql db show -g $resourceGroupName -s $serverName -n $databaseName --query "automaticPauseTime"

But it's returning the error:

(ResourceGroupNotFound) Resource group 'MyResourceGroup' could not be found.
Code: ResourceGroupNotFound
Message: Resource group 'MyResourceGroup' could not be found.

Even if I put the right Resource Group name.

So I tried with T-SQL:

USE master;

DECLARE @databaseName NVARCHAR(128) = 'YourDatabaseName'; -- Replace with your database name
DECLARE @lastConnectionTime DATETIME;

-- Get the last connection time from the sys.dm_exec_sessions DMV
SELECT @lastConnectionTime = MAX(login_time)
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(@databaseName);

IF @lastConnectionTime IS NULL
BEGIN
    PRINT 'The database has never been connected since the last restart or creation.';
END
ELSE
BEGIN
    PRINT 'The database last had a connection at: ' + CONVERT(NVARCHAR, @lastConnectionTime);
END

Which returns The database has never been connected since the last restart or creation. which is a lie because I'm connected to it right now.

So how can I check the last time an Azure SQL Database went on Auto-Pause and for how long?

Answer 1

I had the same question, but then I noticed Activity Log. It's possible to drill into the change details for the DB.

in the Azure Portal, go to the DB > Activity Log > choose an event (pausing & resuming are events, I found out). look for Properties.Status, properties.pausedDate, properties.ResumedDAte or something similar to get the last time paused or resumed.

These details are change activities logged to Azure itself in the DB config. if you don't have permissions to see what history and values that have changed, you may not have the ability to see that information.