Credits:
Author: ChillyDBA
Date: 18 Sep 2008
Description
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
Code
Function:
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 ()
*/
|
 Updating...
Andy Hughes, Aug 23, 2012, 10:49 AM
|