SQL Server uptime query

06 February,2017 by Jack Vamvas

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)

 

Read More

SQL Server - Shrink tempdb without sql server restart (SQL Server ...

Find number of connection attempts since SQL Server start ...

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server uptime query


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer