info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL - Get SQL Server Up-Time - Services, SysProcesses & TempDB
by BF (Principal Consultant; Architecture; Engineering)
2017-07-29









T-SQL - Get SQL Server Up-Time - Services, SysProcesses & TempDB:


Select
'Services' as 'Target',
@@SERVERNAME As 'Server',
ServiceName as 'Service',
Status_Desc as 'Service Status',
last_startup_time as 'Service Last Started',
--Convert Seconds to Days(Integer Division, thus Remainder removed - use Decimal if needed see Remainder):
CAST(DATEDIFF(Second,Convert(Datetime,last_startup_time),GetDate())/86400 as Varchar) + ' Days ' + '| ' +
--T-SQL Modulo(%) to get Remainder Secs | Convert to Hours:
CAST(DATEDIFF(Second,Convert(Datetime,last_startup_time),GetDate())%86400/3600 as Varchar) + ' Hours ' + '| ' +
--T-SQL Modulo(%) to get Remainder Hours | Convert to Mins:
CAST(DATEDIFF(Second,Convert(Datetime,last_startup_time),GetDate())%86400%3600/60 as Varchar) + ' Minutes ' + '| ' +
--T-SQL Modulo(%) to get Remainder Mins | Convert to Secs:
CAST(DATEDIFF(Second,Convert(Datetime,last_startup_time),GetDate())%86400%3600%60 as Varchar) + ' Seconds' AS 'Service Up-Time'
,Startup_Type_Desc as 'Startup Mode'
,Service_Account as 'Service Account'
,[FileName]
From sys.dm_server_services


Select 'Sysprocesses' as 'Target', Min([login_time]) as 'Min Sysprocesses Login Time' From sysprocesses;


Declare @TempDB datetime
Set @TempDB = (Select create_date AS StartTime FROM sys.databases WHERE name = 'tempdb');
Select 'TempDB' as 'Target', @TempDB as 'TempDB Created [Accurate as long TempDB was created and not recovered]';