Azure SQL DB Startup time
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
Why write a post about this?
The traditional methods used for to find the start up time for SQL Server don’t work in Azure SQL DB.
I searched high and low to find this and thought I’ve got to share, and hopefully make it search engine friendly. A traditional google or bing search wasn’t bringing up the best way to find this out. I saw a lot of complicated queries to pull data, convert it and estimate start up time using functions and all kinds of magic. Actually, it turns out that the start up time is provided for you in
sys.dm_os_sys_info (which works in Azure SQL DB). It also work in SQL Server which is a bonus.
Permissions to check Azure SQL DB Startup time
The permissions you need to run this query vary depending on the pricing tier of your Azure SQL DB. I ran this successfully on a P2 and a basic tier database.
On SQL Server, requires VIEW SERVER STATE permission.https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-ver15
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the Server admin or an Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.
I hope this one liner t-sql script was useful. Please let me know in the comments if you know a better way to get the start up time of Azure SQL DB, using T-SQL.
While you’re on my site, why not take a look to see if you can save money on your Azure SQL DBs, and other Azure resources by looking at my guide: 7 ways for data teams to save money in Azure.
Featured image by congerdesign from Pixabay
[…] John McCormack has a tip for us: […]