How to check uptime of SQL Server using TSQL (programmatically)?
Easiest option to find uptime of a SQL Server instance is referring SQL Server log. However you may use following code to find the same data programmatically:
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
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 <> running'
END
PRINT 'SQL Server and SQL Server Agent both are running'
END
Note : This code is referred from http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3d7aa13d-61a7-48d2-ada6-2398b31feb39. All credit and rights of this code goes to it’s original author.
Comments