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