Technical Article

Get SQLServer Name and Uptime SPROC

,

DECLARE @return_value int

EXEC @return_value = [GetSQLUptime]

SELECT 'Return Value' = @return_value

GO

 

Output is as follows:

ServerName SQLUptime_Days_Hours_Minutes

--------------------------------------------------------------------------------
DATABASESERVER1 0 Days, 20 Hours, 06 Minutes

 

 

-- =============================================
-- Author:        Chris Gillham
-- Create date: 11/08/2010
-- Description:    Return SQL Server Name and Uptime
-- Original uptime logic author: ALZDBA
-- Modified from http://www.sqlservercentral.com/Forums/Topic490411-8-1.aspx
-- Updated 11/16/2010 based on input from nigel.
-- =============================================
CREATE PROCEDURE [GetSQLUptime]

AS

BEGIN

    SET NOCOUNT ON;


    Declare @theMinutes int

    Set @theMinutes = (SELECT TOP 1 DATEDIFF(mi,login_time, GETDATE()) AS TotalUpTimeInMinutes
    FROM master..sysprocesses (NOLOCK)
    ORDER BY login_time)


    Select @@SERVERNAME AS ServerName, convert(varchar(15), @theMinutes / 1440 ) 

         + ' Days, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 )

         + ' Hours, ' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60)) 

            + ' Minutes' AS SQLUptime_Days_Hours_Minutes

END

Rate

4.5 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (6)

You rated this post out of 5. Change rating