How To Check SQL SERVER Uptime Through T-SQL

Introduction

Services uptime can be checked through WMI scripts and other methods also. As a DBA most of us would like to know the uptime of SQL Server, i.e how much time is SQL Server running till the server is on. You can do almost all the stuffs in T-SQL, hence Im writing the script in T-SQL to find out SQL Server Uptime. You can also use this script to check SQLServer service and SQLAgent server are running status!

SQL Script:

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM master..sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent is not running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Output:

SQL Server “SAGARSYS” is Online for the past 290 hours & 7 minutes
SQL Server is running but SQL Server Agent is not running


Posted

in

by

Comments

One response to “How To Check SQL SERVER Uptime Through T-SQL”

  1. Joe G. avatar
    Joe G.

    Thanks! This is just what I was looking for. Short and sweet, but reliable.

Leave a Reply

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