When Did My Azure SQL Database Server Restart?

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:

I’m using an average here because each of those waits actually reports slightly different numbers as shown here:

How to get the Azure SQL Database restart date/time

How to get the Azure SQL Database restart date/time

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.)

Previous Post
Clustered Index key columns in Nonclustered Indexes
Next Post
Consulting Lines: “I have a hard stop at…”

20 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

    Reply
  • 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.

    Reply
    • 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. 😀

      Reply
  • Thanks Brent, just embarking on a sql azure adventure and all hint and tips are very useful !

    Reply
  • 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.

    Reply
    • And I think I meant MIN, not MAX…whichever one gets us the oldest date is what I was getting at 🙂

      Reply
    • 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!

      Reply
  • 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.

    Reply
  • Monte Kottman
    August 13, 2015 3:19 pm

    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’

    Reply
    • Monte – and did you test that in Azure SQL DB? (There’s a reason I’m asking, heh.)

      Reply
      • Monte Kottman
        August 13, 2015 3:31 pm

        No.

        Reply
        • 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.

          Reply
    • 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.

      Reply
  • 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.

    Reply
  • 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)

    Reply
  • […] 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 […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}