Author:    ChillyDBA

Date:        18 Sep 2008

This function doesn't use any DMV or calls to OS functions, but obtains the information from within SQL Server.

TempDB is created from scratch on every restart, so the creation date for this database equates almost exactly to the time that the server was last restarted.  It may be a few seconds out, but for all but the most demanding scenarios, this date is acceptable.

The SQL  Agent Service status relies on the fact that the service polls msdb on a regular basis and therefore maintains an open (but usually dormant) connection.  The code checks for the presence of this connection.

You'll note that the check matches for 2 possible application name strings - this is because the name changed between SQL 2005 and 2008.  I have not yet been able to check the SQL 2012 name for the connection

DROP FUNCTION dbo.udf_GetSQLServerUptime GO CREATE FUNCTION dbo.udf_GetSQLServerUptime() RETURNS @SQLServerUptime TABLE    (        LastStarted         DATETIME,        Days                INT,        Hours               INT,        Minutes             INT,        IsAgentRunning      INT    ) AS /*************************************************************** Purpose:   To return the SQL Uptime            and whether the SQL Agent is currently running            The following DMVs are used:                sys.dm_os_sys_info      - contains OS version information                sys.dm_os_ring_buffers  - contains a record of all currently active ring bufers and their contents                    Author:        ChillyDBA History:   18 Sep 08 - Initial Issue ****************************************************************/ BEGIN    DECLARE                @CrDate     DATETIME,            @Today      DATETIME,            @RunTime    DATETIME,            @Days       INT,            @Hours      INT,            @Minutes    INT,            @Seconds    INT,            @AgentRunning   INT    -- get the creation date of tempDB - this iscreated afresh every time the serfver is started so serves well here    SELECT @CrDate = CrDate    FROM master..SysDatabases (NOLOCK)    WHERE Name = 'TempDB'    SELECT @Today = GETDATE()    SELECT  @RunTime = @Today - @CrDate    -- Get the number of days uptime    SELECT @Days = DATEPART(dd, @RunTime)    SELECT @Hours = DATEPART(hh, @RunTime)    SELECT @Minutes = DATEPART(mi, @RunTime)       IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name IN ('SQLAgent - Generic Refresher', 'SQLAgent - Job invocation engine'))        SELECT @AgentRunning = 1    ELSE        SELECT @AgentRunning = 0    INSERT @SQLServerUptime    (        LastStarted,        Days,        Hours,        Minutes,        IsAgentRunning      )    SELECT        @CrDate         AS LastStarted,        @Days           AS Days,        @Hours          AS Hours,        @Minutes        AS Minutes,        @AgentRunning   AS IsAgentRunning           RETURN END /* SELECT * FROM dbo.udf_GetSQLServerUptime () */