[ create a new paste ] login | about

Link: http://codepad.org/hd4XM8Yg    [ raw code | fork ]

Plain Text, pasted on Sep 20:
SELECT
	R.session_id AS spid
,	DatabaseName = DB_NAME(R.database_id)
,	R.blocking_session_id AS blockby
,	R.percent_complete
,	R.Status
,	R.Command
,	T.text AS QueryText
,	SUBSTRING(
		T.text
	,	(R.statement_start_offset/2) + 1
	,	(
			(
				CASE
					WHEN R.statement_end_offset < 1 THEN DATALENGTH(T.text)
					ELSE (R.statement_end_offset - R.statement_start_offset) / 2
				END
			)
		) + 1
	) AS StatementText
,	tat.transaction_id AS TranID
,	tat.name AS TranName
,	tat.transaction_begin_time
,	CONVERT(VARCHAR, ( r.total_elapsed_time / 1000 / 3600 )) + 'h ' +
	CONVERT(VARCHAR, ( ( r.total_elapsed_time / 1000 ) % 3600 ) / 60) + 'm ' +
	CONVERT(VARCHAR, ( r.total_elapsed_time / 1000 ) % 60) + 's' AS elapsed_time
--,	R.total_elapsed_time / 1000 AS total_elapsed_time_sec
,	R.last_wait_type
,	R.wait_resource
--,	r.statement_start_offset
--,	r.statement_end_offset
,	sp.hostname
,	sp.program_name
,	sp.nt_domain
,	sp.nt_username
FROM
	sys.dm_exec_requests R
	CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
	LEFT JOIN sys.dm_tran_session_transactions tst
		JOIN sys.dm_tran_active_transactions tat
			ON	tst.transaction_id = tat.transaction_id
		ON	R.session_id = tst.session_id
	JOIN sys.dm_exec_sessions s
		ON	r.session_id = s.session_id
	JOIN sys.sysprocesses sp
		ON	R.session_id = sp.spid



Create a new paste based on this one


Comments: