Nov
30
2011

T-SQL Script to check SQL Server up time and SQL agent status

While searching for a script which can help in checking SQL server up time and SQL server agent status, came across following good article by Vidhya Sagar

Article: http://sql-articles.com/scripts/how-to-check-sql-server-uptime-through-t-sql/

I have modified given script to some level to include SQL server name and output in tabular format, hope it helps to serve purpose better,

Script: It works in all version of SQL servers (2000, 2005, 2008, 2008 R2)



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
SELECT serverproperty('machinename') as N'Server Name', serverproperty('instancename') as N'instance name', @hr as N'Online since (time in hours)',@min as N'Online since (time in mins)'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT
' " '+CONVERT(VARCHAR(20),SERVERPROPERTY('machinename')) + '"SQL Server is running but SQL Server Agent is not running"'
END
ELSE
BEGIN
PRINT
' " '+CONVERT(VARCHAR(20),SERVERPROPERTY('machinename')) + '"SQL Server and SQL Server Agent both are running"'
END


Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 195 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2019
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
262728293031  

View Post by Categories

%d bloggers like this: