In Azure SQL Database, no one can hear you scream query common system objects that tell you when a restart happened. You don’t get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases.
The closest I’ve been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with clock time – meaning, for every minute on the clock, we get about one minute of these waits.
If we subtract those waits from today’s date, we can get a rough idea of when the server restarted:
|
1 2 3 |
SELECT DATEADD(ms, AVG(-wait_time_ms), GETDATE()) AS approximate_restart_date FROM sys.dm_os_wait_stats w WHERE wait_type IN ('DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP','LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT') |
I’m using an average here because each of those waits actually reports slightly different numbers as shown here:
Best I can tell, this SQL Server restarted on July 14th at around 3:09-3:16AM, and the average reports 2015-07-14 03:11:51.570.
Who cares when the server restarted? sp_BlitzFirst® cares – because folks have started asking me why their Azure SQL Database is slow, so I made it Azure-friendly. Now when you use the @Seconds = 0 parameter, you can see overall wait stats since server restart – just like your on-premise SQL Servers. (ASD also has sys.dm_db_wait_stats, but I like seeing what the server’s overall bottleneck is too.)
Updated version from Henrik Stein Poulsen
Henrik shared this newer version that uses quartiles:
|
1 2 3 4 5 6 7 8 9 |
-- median in SQL Server: --https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server select dateadd(s, (- max(my_column) / 1000), GETDATE()) AS Median_restart_date, decile from (select wait_time_ms as my_column, ntile(10) over (order by wait_time_ms) as decile from sys.dm_os_wait_stats where wait_type in (N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'LAZYWRITER_SLEEP',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'REQUEST_FOR_DEADLOCK_SEARCH',N'HADR_TIMER_TASK',N'CHECKPOINT_QUEUE',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',N'XE_TIMER_EVENT',N'PVS_PREALLOCATE',N'QDS_ASYNC_QUEUE',N'SP_SERVER_DIAGNOSTICS_SLEEP',N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',N'DIRTY_PAGE_POLL',N'FT_IFTS_SCHEDULER_IDLE_WAIT') ) i where decile between 3 and 7 group by decile |
21 Comments. Leave new
Nice one thanks you..
we also love to have the error log it self… can you query by nice trick too? 🙂
pini
Would a version of your “Canary Table” could provide a more accurate estimate? Create a startup procedure that creates a table (perhaps in master) and inserts the current time.
Ray – bad news. As you embark on your Azure journey, you’re about to learn a lot about the ability to put things in master and have things run at “startup” – because startup means different things in Azure. 😀
Thanks Brent, just embarking on a sql azure adventure and all hint and tips are very useful !
Is there a reason you chose to use AVG instead of MAX to find the restart date? My thinking is that the date which is the furthest back is likely closest to the restart than the middle or most recent date. I mean, sure they’re only 5 minutes apart but when trying to correlate something to a date/time, 5 minutes can have a LOT of events in them.
And I think I meant MIN, not MAX…whichever one gets us the oldest date is what I was getting at 🙂
Number2 – in theory, yes. In practice, no, because these waits aren’t exactly 1:1 with clock time. It’s possible that you could have more waits than you have seconds on the clock (like when a wait is experienced across multiple cores simultaneously). Since we don’t know enough about Azure’s internals, we have to be safe and assume an average.
I’ve seen oddball cases where one of these waits was much higher or much lower than others, and something odd had clearly happened on the server, so the average is a little safer.
Great question though!
Brett,
Would having a SQL Agent job that sends an email message when the Agent is restarted (which we do on our production servers that we have no access to after hours) work to get a more accurate time? Of course, this assumes that you have SQL Agent configured to start automatically when the server is restarted. Not sure how this runs in Azure, not there yet.
@DBATrollman – yep, time to bust out the documentation on a product before you start giving answers, hahaha. There’s no Agent in Azure. Thanks though!
A less accurate way, but still effective would be to look at the creation date of the tempdb:
select create_date from sys.databases where name=’tempdb’
Monte – and did you test that in Azure SQL DB? (There’s a reason I’m asking, heh.)
No.
Yeah, I kinda had a hunch. That doesn’t work in Azure SQL DB. But thanks for suggesting it!
Just in the future (not just you, for the other commenters too) – be really careful when you give advice around Azure SQL DB. A lot – A LOT – of the DBA tricks you’ve come to rely on in SQL Server aren’t actually available in Azure SQL DB.
Just as an FYI for Monte: The actual last startup date for the SQL Server service (because sometimes people do just restart the services) can be obtained using this query (2008 R2 SP1 and above, but not for Azure though):
select last_startup_time from sys.dm_server_services where LOWER(filename) like ‘%sqlservr.exe%’
The LOWER caters for case sensitive server collation, and it has been tested against all versions it works for.
Great tip, it’s kinda fun trying to find creative solutions to getting data from Azure we have taken for granted with on-premise. I ran into an arithmetic overflow error with this one due the length of time our instance had been up (nearly a month). Changing the DATEADD from milliseconds to seconds, and dividing the AVG by 1000, to keep it within an INT range instead of BIGINT, has fixed that.
Hi Brent: late reply but the list of wait types in your image does not match those in the query text.
Also, there seems to be very high variation for these 2 wait types (BROKER_TASK_STOP, XE_DISPATCHER_WAIT) compared to the others (like 2 weeks difference)
Yep, make sure to read the post for the explanation – the wait stats I use change over time as MS tweaks Azure. They don’t give us an official, sanctioned way to get the startup time.
Hi Brent . I did read the entire post. Several times. I was hoping it would be approximate rather than inaccurate.
The time, not the post!
[…] Ozar posted a way to get the approximate Azure SQL Database restart date/time but I found that some of the wait types can produce large outliers and skew the […]
John McCormac has a new approach, that is probably better: See
https://johnmccormack.it/2021/02/how-to-find-out-azure-sql-db-startup-time/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-find-out-azure-sql-db-startup-time
or just run
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;