Find SQL Server Start Time Quickly

DECLARE @WorkingVariable datetime

SELECT @WorkingVariable = login_time
FROM master..sysprocesses
WHERE cmd='LAZY WRITER'

print @WorkingVariable

The above will simply return the date when the LAZY Writer process started.

SELECT create_date FROM sys.databases WHERE NAME='tempdb'

The above simply checks when Tempdb started

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()))
ELSE
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
  PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))
    +'" has been Online for the past '+@hr+' hours & '+@min+' minutes.'
    + 'Instance start time is ' + CONVERT(varchar(23),@crdate, 113)

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses 
                            WHERE program_name like N'SQLAgent90%')
 BEGIN
   PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
 END
 ELSE BEGIN
   PRINT 'SQL Server and SQL Server Agent are both running'
 END

The above produces user friendly information on how many hours the Server has been up for, and other useful info.

image

Advertisements

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

%d bloggers like this: