How To Know Uptime Of SQL Server By TSQL

How To Know Uptime Of SQL Server By TSQL

Hi friends, in this post we will discuss how to know uptime of SQL Server by TSQL or you can say how to check sql server uptime through tsql.

Earlier we had discussed about SQL Server Schema OwnershipHow to create a database in sql server with TsqlEnable TCP/IP Protocol in SQL Server Configuration Manager Using Command Prompt and SQL Server Interview Questions & Answers 3.

Code:

Use master
go
DECLARE @startdate DATETIME, @hour VARCHAR(50), @minute VARCHAR(5)

SELECT @startdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hour=(DATEDIFF ( mi, @startdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @startdate,GETDATE()))/60)=0

SELECT @minute=(DATEDIFF ( mi, @startdate,GETDATE()))

ELSE

SELECT @minute=(DATEDIFF ( mi, @startdate,GETDATE()))-((DATEDIFF( mi, @startdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hour+' hours & '+@minute+' minutes'

Second Way when Trace Flag 3609 is used:

Code:

Use master
go
DECLARE @startdate DATETIME, @hour VARCHAR(50), @minute VARCHAR(5)

SELECT @startdate=sqlserver_start_time from sys.dm_os_sys_info

SELECT @hour=(DATEDIFF ( mi, @startdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @startdate,GETDATE()))/60)=0

SELECT @minute=(DATEDIFF ( mi, @startdate,GETDATE()))

ELSE

SELECT @minute=(DATEDIFF ( mi, @startdate,GETDATE()))-((DATEDIFF( mi, @startdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hour+' hours & '+@minute+' minutes'