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

Leave a Reply

Leave a comment

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

*


x

Related Posts

Batch script to deploy multiple SQL files
I was working on a project where I’m supposed to deploy lots of SQL Server script files (.sql). It’s very tedious job either to manually execute ...
CSSUG October 2019 Meet
We are proud to share CSSUG is getting resumed after couple of years. All the hard work and initiation is done by Madhivanan. Thanks to Madhi fo...
Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
powered by RelatedPosts