RSS

Script to check how long the SQL Services were up

16 Sep

— ***********************************************************************************/
— Description  :    Script to check  how long the SQL Services were up  
—                        Following script will list down time for SQL Services are running and Current Status of SQL Server Agent too
— ***********************************************************************************/
SET NOCOUNT ON
DECLARE @crdate

 DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
   SELECT @crdate=crdate FROM 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 <<NOT>> running’
      END
   ELSE
      BEGIN
         PRINT ‘SQL Server and SQL Server Agent both are running’
END
go

 

 
Leave a comment

Posted by on September 16, 2011 in SQl server Administration

 

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