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

T-SQL to get various details on currently running Active processes on SQL Server Instance

Couple of day back i was working on something very important which will run some stored procedures to accomplish the task. Now there can be many transactions or different piece of codes in a Stored Procedure. Now just think that you come across a weird situation where you are not getting your results on time or may be its taking more time than expected. Now you can determine which is session and spid your procedure is running, but what if you want to know which piece of code is running which might me causing the issue, since how long it is running etc these kind of various questions comes to mind and we are stuck. Now thanks to DMV's and DMF's which makes our task easier by giving various piece of information which will be very useful for us. So i came with a small piece of TSQL which will answer these kind of questions and gives us the vital information.

This also provides information on estimated completion time, completed percentage for various operations like Backup,Database Integrity Check, Index Re-Organise etc.

USE MASTER

GO

SELECT SPID,ER.percent_complete,

/* This piece of code has been taken from article. Nice code to get time criteria's

http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx

*/CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */
ER
.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,

DB_NAME(SP.DBID) AS DBNAME,

SUBSTRING(est.text, (ER.statement_start_offset/2)+1,

((CASE ER.statement_end_offset

WHEN -1 THEN DATALENGTH(est.text)

ELSE ER.statement_end_offset

END - ER.statement_start_offset)/2) + 1) AS QueryText,

TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,

SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME

FROM SYSPROCESSES SP

INNER JOIN sys.dm_exec_requests ER

ON sp.spid = ER.session_id

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST

ORDER BY CPU DESC