T-SQL to get the SQL Server Service Start time and Up time of SQL Server service

There are number of occasions where we need to get to know the SQL Server service start date time and also from what time the SQL Services are up and running generally termed as Uptime. There are many ways to do it like check for temp DB Creation time, using DMF sys.dm_io_virtual_file_stats etc. Below written is small T-SQL script but very useful which provides you this information. Hope this is useful. Comments and suggestions are always welcome.

USE MASTER
GO
SET NOCOUNT ON
GO
SELECT 'SQL server started at ' + 
CAST((CONVERT(DATETIME, sqlserver_start_time, 126)) AS VARCHAR(20))
+' and is up and running from '+
CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5)) 
+ ' hours and ' +
RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2) 
+ ' minutes' AS [Start_Time_Up_Time] FROM sys.dm_os_sys_info
GO
SET NOCOUNT OFF
GO 
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