Question: Is there a reliable method to extract the sql server uptime? I’ve tried different methods , such as xp_readerrorlog , but I discovered a limitation when the error log is recycled , updated date & time information is added to the error log , which is the time \ date of the error log recycle , but not the sql server instance restart.
Answer: There are two methods I use to for a sql server uptime query. Method 1 extracts the sql server start time from the sys.dm_os_sys_info DMV. Method 2 utilises the tempdb create date , as the tempdb is created new everytime sql server restarts.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info; SELECT create_date AS SQLServerStartTime FROM sys.databases WHERE name = 'tempdb';
When you execute these queries on a SQL Server – there will be a slight difference , Method 2 – is normally a few seconds after Method 1. The reason is the tempdb creation step happens after the sql server restart, but not much can happen without tempdb in action
If you need to iterate through a large number of SQL Servers and record the start time , read this post which explains how to do it SQL Server restart times using Powershell (SQL Server DBA)