SQL server

SQL uptime and agent status.

Below is a script that can be used to find out the uptime of your SQL DB server and whether if the SQL agent is running or not. Restart of the SQL DB server without a physical server restart will also reset this time.

Script found on www.sqlservercentral.com

SET NOCOUNT ON
DECLARE
@crdate DATETIME
, @days VARCHAR(3)
, @hr VARCHAR(50)
, @min VARCHAR(5)

SELECT @crdate=crdate FROM master.dbo.sysdatabases WHERE NAME=’tempdb’

SELECT @days=((DATEDIFF ( mi, @crdate,GETDATE()))/60)/24

SELECT @hr=((DATEDIFF ( mi, @crdate,GETDATE()))/60)- (@days * 24)

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 ‘
+@days+’ days & ‘
+@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
ELSE
BEGIN
PRINT ‘SQL Server and SQL Server Agent both are running’
END

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s