Show sessions and locks on server.sql

/*

This script shows current status and activity for currently running tasks on
the SQL Server. It returns 6 recordsets, in the following order:

1: General server-wide information
2: Database files, usage, disk space
3: Connections by SPID including CPU and I/O totals
4: Running queries, memory use/grant, query plan
5: Open transactions, type, isolation level
6: Locks by object, partition, including size, wait type, blocking info.

Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/

Source:  http://sqlsunday.com/downloads/
Version: 2018-10-26

DISCLAIMER: This script does not make any modifications to the server, except
            for creating three temporary functions in tempdb, used to format
	    values in a user-friendly manner. However, the script may not be
	    suitable to run in a production environment. I cannot assume any
	    responsibility regarding the accuracy of the output information,
	    performance impacts on your server, or any other consequence. If
	    your juristiction does not allow for this kind of waiver/disclaimer,
	    or if you do not accept these terms, you are NOT allowed to store,
	    distribute or use this code in any way.

*/

USE tempdb;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET DEADLOCK_PRIORITY LOW;
SET STATISTICS XML OFF;
SET LOCK_TIMEOUT 1000;
SET NOCOUNT ON;

DECLARE @hasPermissions bit=HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE');
IF (@hasPermissions=0) BEGIN;
	RAISERROR('This script requires VIEW SERVER STATE permissions.', 16, 1);
	RETURN;
END;

SET @hasPermissions=HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'CREATE FUNCTION')
IF (@hasPermissions=0) BEGIN;
	RAISERROR('This script requires CREATE FUNCTION permissions in tempdb.', 16, 1);
	RETURN;
END;

-------------------------------------------------------------------------------








BEGIN TRANSACTION;
BEGIN TRY;

-------------------------------------------------------------------------------
--- Temporary functions, used to beautify numbers, bits&bytes and dates.
-------------------------------------------------------------------------------

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_age'))
	EXEC('
CREATE FUNCTION dbo.fn_friendly_age (
	@from	datetime,
	@to	datetime)
RETURNS nvarchar(20)
WITH SCHEMABINDING
AS

BEGIN
	DECLARE @age nvarchar(20)

	IF (@from IS NOT NULL AND @to IS NOT NULL)
		SET @age=(CASE
			WHEN DATEDIFF(ss, @from, @to)<1
			THEN ''       ''+STR(DATEDIFF(ms, @from, @to), 10, 0)+'' ms.''

			WHEN DATEDIFF(ss, @from, @to)<180
			THEN ''        ''+STR(0.001*DATEDIFF(ms, @from, @to), 10, 1)+'' s.''

			WHEN DATEDIFF(ss, @from, @to)<24*3600
			THEN ''     ''+SUBSTRING(CONVERT(nvarchar(20), DATEADD(ss, DATEDIFF(ss, @from, @to), 0), 120), 12, 8)

			WHEN DATEDIFF(dd, @from, @to)<3
			THEN STR(FLOOR(1.0*DATEDIFF(ss, @from, @to)/(3600*24)), 3, 0)+''d+''+
				SUBSTRING(CONVERT(nvarchar(20), DATEADD(ss, DATEDIFF(ss, @from, @to), 0), 120), 12, 5)+''  ''

			ELSE STR(FLOOR(1.0*DATEDIFF(ss, @from, @to)/(3600*24)), 3, 0)+''d ''+
				REPLICATE('' '', 5)+''  ''
			END)

	RETURN @age
END');

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_format_number'))
	EXEC('
CREATE FUNCTION dbo.fn_format_number(
	@number		numeric(28, 8),
	@prec		tinyint)
RETURNS nvarchar(100)
WITH SCHEMABINDING
AS

BEGIN
	DECLARE @out nvarchar(100), @offset int=(CASE WHEN @prec=0 THEN 0 ELSE @prec+1 END)

	SET @out=REVERSE(LTRIM(ISNULL(STR(@number, 20, @prec), '''')))
	WHILE (@offset<LEN(@out)) BEGIN
		IF (SUBSTRING(@out, @offset, 1)!=''.'') BEGIN
			SET @out=LEFT(@out, @offset)+'' ''+SUBSTRING(@out, @offset+1, LEN(@out))
			SET @offset=@offset+1
		END
		SET @offset=@offset+3
	END
	SET @out=LTRIM(REVERSE(@out))
	IF (LEN(@out)<15)
		SET @out=NULLIF(RIGHT(REPLICATE('' '', 15+@prec)+@out, 15+@prec), '''')

	RETURN @out
END');

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_size'))
	EXEC('
CREATE FUNCTION dbo.fn_friendly_size (
	@bytes	bigint)
RETURNS nvarchar(20)
WITH SCHEMABINDING
AS

BEGIN;
	DECLARE @size nvarchar(20), @k bigint=1024;

	IF (@bytes IS NOT NULL)
		SET @size=(CASE
			--WHEN @bytes<4*@k
			--THEN dbo.fn_format_number(1.0*@bytes, 0)+'' B''

			WHEN @bytes<4*@k*@k
			THEN dbo.fn_format_number(1.0*@bytes/@k, 1)+'' kB''

			WHEN @bytes<4*@k*@k*@k
			THEN dbo.fn_format_number(1.0*@bytes/@k/@k, 2)+'' MB''

			WHEN @bytes<4*@k*@k*@k*@k
			THEN dbo.fn_format_number(1.0*@bytes/@k/@k/@k, 2)+'' GB''

			ELSE dbo.fn_format_number(1.0*@bytes/@k/@k/@k/@k, 2)+'' TB''
			END);

	RETURN @size;
END;');



-------------------------------------------------------------------------------
--- Gather information on database files and objects/indexes
-------------------------------------------------------------------------------

DECLARE @sql nvarchar(max), @sql2 nvarchar(max), @sql3 nvarchar(max),
	@sql4 nvarchar(max), @name sysname, @virtual_machine_type int,
	@version nvarchar(4), @physical_memory bigint, @virtual_memory bigint,
	@gb bigint=1024*1024*1024, @mb bigint=1024*1024,
	@max_server_memory bigint, @min_server_memory bigint, @max_degree_of_parallelism tinyint;

SET @version=LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(10)), 4);
IF (@version LIKE '9%') SET @version=LEFT('0'+@version, 4);

SELECT @max_server_memory=CAST([value] AS bigint)*1024*1024 FROM sys.configurations WHERE [name]='max server memory (MB)';
SELECT @min_server_memory=CAST([value] AS bigint)*1024*1024 FROM sys.configurations WHERE [name]='min server memory (MB)';
SELECT @max_degree_of_parallelism=CAST([value] AS tinyint)  FROM sys.configurations WHERE [name]='max degree of parallelism';

IF (@version<'11.0')
	EXECUTE sp_executesql N'
		SELECT @physical_memory=physical_memory_in_bytes,
			@virtual_memory=virtual_memory_in_bytes
		FROM sys.dm_os_sys_info',
		N'@physical_memory bigint OUTPUT, @virtual_memory bigint OUTPUT',
		@physical_memory=@physical_memory OUTPUT,
		@virtual_memory=@virtual_memory OUTPUT;

IF (@version>='11.0')
	EXECUTE sp_executesql N'
		SELECT @physical_memory=physical_memory_kb*1024,
			@virtual_memory=virtual_memory_kb*1024
		FROM sys.dm_os_sys_info',
		N'@physical_memory bigint OUTPUT, @virtual_memory bigint OUTPUT',
		@physical_memory=@physical_memory OUTPUT,
		@virtual_memory=@virtual_memory OUTPUT;

BEGIN TRY;
	EXECUTE sp_executesql N'
		SELECT @virtual_machine_type=virtual_machine_type
		FROM sys.dm_os_sys_info',
		N'@virtual_machine_type int OUTPUT',
		@virtual_machine_type=@virtual_machine_type OUTPUT;
END TRY
BEGIN CATCH;
	PRINT 'This version of SQL Server does not provide virtual_machine_type.';
END CATCH;

-------------------------------------------------------------------------------

DECLARE @block_chain TABLE (
	spid				int NOT NULL,
	blocked_by			nvarchar(max) NOT NULL,
	PRIMARY KEY CLUSTERED (spid)
);

DECLARE @blocking TABLE (
	spid				int NOT NULL,
	blocking			nvarchar(max) NOT NULL,
	PRIMARY KEY CLUSTERED (spid)
);

DECLARE @locks TABLE (
	spid				int NOT NULL,
	tran_id				bigint NOT NULL,
	lock_ord			int NOT NULL,
	mode				nvarchar(255) NOT NULL,
	[database]			nvarchar(255) NOT NULL,
	[object_id]			int NOT NULL,
	[object_name]		nvarchar(255) NULL,
	index_id			int NOT NULL,
    index_type			tinyint NULL,
	index_name			nvarchar(255) NULL,
	index_filter		nvarchar(max) NULL,
	object_notes		nvarchar(255) NULL,
	obj_rows			bigint NULL,
	partition_number	int NOT NULL,
	partition_count		int NULL,
	partition_boundary	nvarchar(max) NULL,
	request_owner_type	nvarchar(50) NOT NULL,
	wait_type			nvarchar(120) NULL,
	wait_time			nvarchar(100) NULL,
	blkd_by_spid		int NULL,
	[count]				int NULL,
	isWaiting			bit NULL,
	isBlocking			bit NULL,
	ident				int IDENTITY(1, 1) NOT NULL,
	PRIMARY KEY CLUSTERED (spid, tran_id, [object_id], index_id, partition_number, lock_ord, ident)
);

DECLARE @tran_locks TABLE (
	resource_database_id int NOT NULL,
	request_session_id	int NOT NULL,
	request_owner_id	bigint NULL,
	request_mode		nvarchar(120) NOT NULL,
	request_status		nvarchar(120) NOT NULL,
	resource_type		nvarchar(120) NOT NULL,
	request_owner_type	nvarchar(120) NULL,
	lock_owner_address	varbinary(8) NOT NULL,
	resource_associated_entity_id bigint NULL,
	ord					int NOT NULL,
	PRIMARY KEY CLUSTERED (request_session_id, ord)
);

INSERT INTO @tran_locks
SELECT resource_database_id, request_session_id, request_owner_id, request_mode, request_status,
	resource_type, request_owner_type, lock_owner_address, resource_associated_entity_id,
	ROW_NUMBER() OVER (PARTITION BY request_session_id ORDER BY lock_owner_address) AS ord
FROM sys.dm_tran_locks WITH (NOLOCK);

DECLARE @tran_session_transactions TABLE (
	transaction_id		bigint NOT NULL,
	session_id			int NOT NULL,
	is_local			bit NOT NULL,
	PRIMARY KEY CLUSTERED (transaction_id, session_id)
);

INSERT INTO @tran_session_transactions
SELECT transaction_id, session_id, is_local
FROM sys.dm_tran_session_transactions WITH (NOLOCK);

DECLARE @exec_sessions TABLE (
	session_id			int NOT NULL,
	[program_name]		nvarchar(256) NULL,
	login_name			nvarchar(256) NOT NULL,
	[status]			nvarchar(60) NOT NULL,
	last_request_start_time datetime NOT NULL,
	cpu_time			int NOT NULL,
	memory_usage		int NOT NULL,
	reads				bigint NOT NULL,
	writes				bigint NOT NULL,
	logical_reads		bigint NOT NULL,
	flags				nvarchar(200) NOT NULL,
	client_interface_name nvarchar(64) NULL,
	PRIMARY KEY CLUSTERED (session_id)
);

INSERT INTO @exec_sessions
SELECT session_id, [program_name], login_name, [status], last_request_start_time, cpu_time,
	memory_usage, reads, writes, logical_reads,
	  'arithabort '+(CASE [arithabort] WHEN 1 THEN 'ON' ELSE 'OFF' END)+
	', quot.id '+(CASE [quoted_identifier] WHEN 1 THEN 'ON' ELSE 'OFF' END)+
	(CASE [ansi_warnings] WHEN 0 THEN ', ansi warn OFF' ELSE '' END)+
	(CASE [ansi_padding] WHEN 0 THEN ', ansi pad OFF' ELSE '' END)+
	(CASE [ansi_nulls] WHEN 0 THEN ', nulls OFF' ELSE '' END)+
	(CASE [concat_null_yields_null] WHEN 0 THEN ', conc.null OFF' ELSE '' END) AS flags,
	LEFT(client_interface_name, CHARINDEX(' ', client_interface_name+' ')-1)
FROM sys.dm_exec_sessions WITH (NOLOCK);

DECLARE @jobs TABLE (
	job_id				uniqueidentifier NOT NULL,
	job_name			sysname NOT NULL,
	step_id				int NOT NULL,
	step_name			sysname NOT NULL,
	search_name			nvarchar(max) NOT NULL,
	PRIMARY KEY CLUSTERED (job_id, step_id)
);

INSERT INTO @jobs
SELECT j.job_id, j.name AS job_name, js.step_id, js.step_name, ' 0x'+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)),  7,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)),  5,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)),  3,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)),  1,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 12,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 10,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 17,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 15,  2)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 20,  4)+
	SUBSTRING(CAST(j.job_id AS nvarchar(max)), 25, 12)+'%:% '+
	CAST(js.step_id AS nvarchar(max))+')' AS search_name
FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobsteps AS js WITH (NOLOCK) ON j.job_id=js.job_id;

DECLARE @exec_requests TABLE (
	session_id			int NOT NULL,
	request_id			int NOT NULL,
	database_id			smallint NOT NULL,
	transaction_isolation_level smallint NOT NULL,
	percent_complete	real NOT NULL,
	total_elapsed_time	int NOT NULL,
	command				nvarchar(32) NOT NULL,
	statement_start_offset int NULL,
	statement_end_offset int NULL,
	plan_handle			varbinary(64) NULL,
    _id                 int IDENTITY(1, 1) NOT NULL,
	PRIMARY KEY CLUSTERED (session_id, request_id, _id)
);

INSERT INTO @exec_requests (session_id, request_id, database_id, transaction_isolation_level, percent_complete,
	total_elapsed_time, command, statement_start_offset, statement_end_offset, plan_handle)
SELECT session_id, request_id, database_id, transaction_isolation_level, percent_complete,
	total_elapsed_time, command, statement_start_offset, statement_end_offset, plan_handle
FROM sys.dm_exec_requests WITH (NOLOCK);

DECLARE @exec_connections TABLE (
	session_id			int NOT NULL,
	client_net_address	nvarchar(48) NULL,
	client_tcp_port		int NULL,
	local_net_address	nvarchar(48) NULL,
	auth_scheme			nvarchar(80) NOT NULL,
	connect_time		datetime NOT NULL,
	num_reads			int NULL,
	num_writes			int NULL,
	most_recent_sql_handle varbinary(64),
	PRIMARY KEY CLUSTERED (session_id)
);

INSERT INTO @exec_connections
SELECT session_id, client_net_address, client_tcp_port, local_net_address, auth_scheme,
	connect_time, num_reads, num_writes, most_recent_sql_handle
FROM sys.dm_exec_connections WITH (NOLOCK)
WHERE endpoint_id!=0 AND session_id IS NOT NULL;

DECLARE @os_tasks TABLE (
    session_id          int NOT NULL,
    scheduler_id        int NOT NULL,
    PRIMARY KEY CLUSTERED (session_id, scheduler_id)
);

INSERT INTO @os_tasks
SELECT DISTINCT session_id, scheduler_id
FROM sys.dm_os_tasks
WHERE session_id IS NOT NULL AND
      scheduler_id IS NOT NULL;

DECLARE @tran_active_transactions TABLE (
	transaction_id		bigint NOT NULL,
	transaction_begin_time	datetime NOT NULL,
	transaction_type	int NOT NULL,
	transaction_state	int NOT NULL,
	PRIMARY KEY CLUSTERED (transaction_id)
);

INSERT INTO @tran_active_transactions
SELECT transaction_id, transaction_begin_time, transaction_type, transaction_state
FROM sys.dm_tran_active_transactions WITH (NOLOCK);

DECLARE @os_waiting_tasks TABLE (
	session_id			int NULL,
	blocking_session_id	int NULL,
	waiting_task_address varbinary(8) NOT NULL,
	wait_type			nvarchar(120) NULL,
	wait_duration_ms	bigint NULL,
	resource_address	varbinary(8) NULL,
	exec_context_id		int NULL,
	dupl				int NOT NULL,
	PRIMARY KEY CLUSTERED (waiting_task_address, dupl)
);

INSERT INTO @os_waiting_tasks
SELECT session_id, blocking_session_id, waiting_task_address,
	wait_type, wait_duration_ms, resource_address, exec_context_id,
	ROW_NUMBER() OVER (
		PARTITION BY waiting_task_address
		ORDER BY wait_duration_ms, resource_address) AS dupl
FROM sys.dm_os_waiting_tasks WITH (NOLOCK);

DECLARE @exec_query_memory_grants TABLE (
	session_id			int NOT NULL,
	request_id			int NOT NULL,
	requested_mb		numeric(16, 3) NOT NULL,
	granted_mb			numeric(16, 3) NULL,
	minimum_required_mb	numeric(16, 3) NOT NULL,
	used_mb				numeric(16, 3) NOT NULL,
	max_used_mb			numeric(16, 3) NOT NULL,
	ideal_mb			numeric(16, 3) NOT NULL,
	est_query_cost		numeric(16, 1) NOT NULL,
	PRIMARY KEY CLUSTERED (session_id, request_id)
);

INSERT INTO @exec_query_memory_grants
SELECT session_id, request_id,
	SUM(1.0*requested_memory_kb/1024) AS requested_mb,
	SUM(1.0*granted_memory_kb/1024) AS granted_mb,
	SUM(1.0*required_memory_kb/1024) AS minimum_required_mb,
	SUM(ISNULL(1.0*used_memory_kb/1024, 0.0)) AS used_mb,
	SUM(ISNULL(1.0*max_used_memory_kb/1024, 0.0)) AS max_used_mb,
	SUM(1.0*ideal_memory_kb/1024) AS ideal_mb,
	SUM(query_cost) AS est_query_cost
FROM sys.dm_exec_query_memory_grants WITH (NOLOCK)
GROUP BY session_id, request_id;

DECLARE @dm_server_services TABLE (
    _id                 tinyint IDENTITY(1, 1) NOT NULL,
    servicename         nvarchar(512) NOT NULL,
    startup_type_desc   nvarchar(512) NOT NULL,
    status_desc         nvarchar(512) NOT NULL,
    service_account     nvarchar(512) NOT NULL,
    cluster_nodename    nvarchar(512) NULL,
    PRIMARY KEY CLUSTERED (_id)
);

IF (OBJECT_ID('sys.dm_server_services') IS NOT NULL) BEGIN;
    BEGIN TRY;
        INSERT INTO @dm_server_services
        SELECT servicename, startup_type_desc, status_desc, service_account, cluster_nodename
        FROM sys.dm_server_services;
    END TRY
    BEGIN CATCH;
        PRINT 'sys.dm_server_services is not supported on this version of SQL Server.';
    END CATCH;
END;

DECLARE @dm_os_buffer_descriptors TABLE (
    database_id         int NOT NULL,
    [file_id]           int NOT NULL,
    [count]             bigint NOT NULL,
    PRIMARY KEY CLUSTERED (database_id, [file_id])
);

IF (OBJECT_ID('sys.dm_os_buffer_descriptors') IS NOT NULL) BEGIN;
    BEGIN TRY;
        INSERT INTO @dm_os_buffer_descriptors
        SELECT database_id, [file_id], COUNT_BIG(*)
        FROM sys.dm_os_buffer_descriptors
        GROUP BY database_id, [file_id]
    END TRY
    BEGIN CATCH;
        PRINT 'sys.dm_os_buffer_descriptors is not supported on this version of SQL Server.';
    END CATCH;
END;

DECLARE @database_procedures TABLE (
	database_id			int NOT NULL,
	[object_id]			int NOT NULL,
	[name]				nvarchar(255) NOT NULL,
	PRIMARY KEY CLUSTERED (database_id, [object_id])
);

DECLARE @database_objects TABLE (
	database_id			int NOT NULL,
	[object_id]			int NOT NULL,
	index_id			int NOT NULL,
	hobt_id				bigint NOT NULL,
	[name]				nvarchar(255) NOT NULL,
	index_type			tinyint NULL,
	index_name			nvarchar(255) NULL,
	partition_number	int NOT NULL,
	[rows]				bigint NULL,
	[type_desc]			nvarchar(255) NOT NULL,
	is_clustered		bit NULL,
	index_filter		nvarchar(max) NULL,
	PRIMARY KEY CLUSTERED (database_id, [object_id], hobt_id)
);

DECLARE @files TABLE (
	database_id			int NOT NULL,
	[file_id]			int NOT NULL,
	filetype			nvarchar(20) NOT NULL,
	[name]				nvarchar(255) NOT NULL,
	drive				char(3) NOT NULL,
	[filegroup]			nvarchar(255) NULL,
	size_mb				numeric(18, 2) NOT NULL,
	autogrow			numeric(18, 2) NULL,
	is_percent_growth	bit NOT NULL,
	max_size_mb			numeric(18, 2) NULL,
	used_size_mb		numeric(18, 2) NULL,
	total_bytes			bigint NULL,
	available_bytes		bigint NULL,
	file_system_type	nvarchar(100) NULL,
    primary_replica     sysname NULL,
    [availability_mode] tinyint NULL,
    suspend_state       nvarchar(255) NULL,
    queue_kb            bigint NULL,
    rpo                 bigint NULL,
	PRIMARY KEY CLUSTERED (database_id, [name])
);

DECLARE @parts TABLE (
	database_id			int NOT NULL,
	[object_id]			int NOT NULL,
	index_id			int NOT NULL,
	hobt_id				bigint NULL,
	boundary_id			int NOT NULL,
	boundary			nvarchar(max) NULL,
	[rows]				int NULL,
	PRIMARY KEY CLUSTERED (database_id, [object_id], index_id, boundary_id)
);

DECLARE #cur CURSOR LOCAL FOR
	SELECT [name],
    	--- Databases, schemas, tables, number of rows
		'SELECT '+CAST(database_id AS nvarchar(max))+', o.[object_id], ISNULL(ix.index_id, 0), p.hobt_id,
			s.name+''.''+o.name, ix.[type] AS index_type, ix.name AS index_name, p.partition_number, p.[rows], o.type_desc,
			(CASE WHEN cix.[object_id] IS NULL THEN 0 ELSE 1 END) AS is_clustered,
			ix.filter_definition
		 FROM ['+[name]+'].sys.objects AS o WITH (NOLOCK)
		 INNER JOIN ['+[name]+'].sys.partitions AS p WITH (NOLOCK) ON o.[object_id]=p.[object_id]
		 INNER JOIN ['+[name]+'].sys.schemas AS s WITH (NOLOCK) ON o.[schema_id]=s.[schema_id]
		 LEFT JOIN ['+[name]+'].sys.indexes AS ix WITH (NOLOCK) ON p.[object_id]=ix.[object_id] AND p.index_id=ix.index_id
		 LEFT JOIN ['+[name]+'].sys.indexes AS cix WITH (NOLOCK) ON p.[object_id]=cix.[object_id] AND cix.[type]=1
		' AS [sql],

		--- Database files
		'USE ['+[name]+'];
		 SELECT '+CAST(database_id AS nvarchar(max))+', f.[file_id], f.type_desc AS filetype,
			f.name, UPPER(LEFT(f.physical_name, 3)) AS drive,
			ds.name AS [filegroup], 1.0*f.size*8/1024 AS size_mb,
			(CASE WHEN f.is_percent_growth=1 THEN 1.0*f.growth
				ELSE 1.0*f.growth*8/1024 END) AS autogrow,
			f.is_percent_growth, ROUND(1.0*NULLIF(f.max_size, -1)*8/1024, 1) AS max_size_mb,
			1.0*FILEPROPERTY(f.name, ''SpaceUsed'')*8/1024 AS used_size_mb, '+
			(CASE WHEN @version>'10.5' OR
				@version='10.5' AND CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(10))>='SP1' THEN '
			v.total_bytes, v.available_bytes, v.file_system_type' ELSE '
			NULL AS total_bytes, NULL AS available_bytes, NULL AS file_system_type' END)+', NULL, NULL, NULL, NULL, NULL
		 FROM ['+name+'].sys.database_files AS f WITH (NOLOCK)
		 LEFT JOIN ['+[name]+'].sys.data_spaces AS ds WITH (NOLOCK) ON f.data_space_id=ds.data_space_id'+
			(CASE WHEN @version>'10.5' OR
				   @version='10.5' AND CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(10))>='SP1' THEN '
		 CROSS APPLY ['+[name]+'].sys.dm_os_volume_stats('+CAST(database_id AS nvarchar(max))+', f.[file_id]) AS v' ELSE '' END)+
                --- In-memory OLTP tables:
                (CASE WHEN @version>='12.0' THEN '
                 UNION ALL
		 SELECT '+CAST(database_id AS nvarchar(max))+', -1 AS [file_id], ''IN-MEMORY'' AS filetype,
			'''' AS [name], ''RAM'' AS drive,
			'''' AS [filegroup], 1.0*SUM(xtp.allocated_bytes)/(1024*1024) AS size_mb,
			NULL AS autogrow,
			0 AS is_percent_growth, NULL AS max_size_mb,
			1.0*SUM(xtp.used_bytes)/(1024*1024) AS used_size_mb,
			NULL AS total_bytes, NULL AS available_bytes, NULL AS file_system_type, NULL, NULL, NULL, NULL, NULL
         FROM ['+[name]+'].sys.dm_db_xtp_memory_consumers AS xtp
         HAVING COUNT(*)>0' ELSE '' END) AS sql2,

		--- Partitioning schemes, functions, boundaries
		'WITH spc (data_space_id, function_id, pf_name, boundary_id, boundary_value_on_right, [type], [value])
		 AS (	SELECT ps.data_space_id, pf.function_id, pf.name AS pf_name, val.boundary_id, pf.boundary_value_on_right,
				CAST(SQL_VARIANT_PROPERTY(val.[value], ''BaseType'') AS varchar(100)) AS [type],
                (CASE SQL_VARIANT_PROPERTY(val.[value], ''BaseType'')
					WHEN ''date'' THEN LEFT(CONVERT(nvarchar(max), val.[value], 120), 10)
					WHEN ''datetime'' THEN CONVERT(nvarchar(max), val.[value], 120)
					ELSE CAST(val.[value] AS nvarchar(max))
				END) AS [value]
			FROM ['+[name]+'].sys.partition_range_values AS val WITH (NOLOCK)
			INNER JOIN ['+[name]+'].sys.partition_functions AS pf WITH (NOLOCK) ON val.function_id = pf.function_id
			INNER JOIN ['+[name]+'].sys.partition_schemes AS ps WITH (NOLOCK) ON pf.function_Id=ps.function_id),

		     rng (pf_name, data_space_id, boundary_id, lower_rng, upper_rng)
		 AS (	SELECT	ISNULL(a.pf_name, b.pf_name),
				ISNULL(a.data_space_id, b.data_space_id),
				ISNULL(a.boundary_id+1, b.boundary_id),
				(CASE WHEN RIGHT(ISNULL(a.[type], b.[type]), 3) IN (''int'', ''bit'') AND TRY_CAST(a.[value] AS bigint)+1=TRY_CAST(b.[value] AS bigint)
                      THEN ''''
                      ELSE a.[value]+(CASE
					                  WHEN a.boundary_value_on_right=1 THEN ''<=''
					                  WHEN a.boundary_value_on_right=0 THEN ''<'' END) END),
				(CASE WHEN RIGHT(ISNULL(a.[type], b.[type]), 3) IN (''int'', ''bit'') AND TRY_CAST(a.[value] AS bigint)+1=TRY_CAST(b.[value] AS bigint) THEN ''=''+a.[value]
                      WHEN b.boundary_value_on_right=1 THEN ''<''+b.[value]
					  WHEN b.boundary_value_on_right=0 THEN ''<=''+b.[value] END)
			FROM spc AS a
			FULL JOIN spc AS b ON
				a.function_id=b.function_id AND
				a.boundary_id+1=b.boundary_id)
		SELECT '+CAST(database_id AS nvarchar(max))+', ix.[object_id], ix.index_id, p.hobt_id, ISNULL(rng.boundary_id, 0),
			ISNULL(rng.lower_rng, '''')+c.name+ISNULL(rng.upper_rng, '''') AS boundary, p.[rows]
		FROM rng
		INNER JOIN ['+[name]+'].sys.indexes AS ix WITH (NOLOCK) ON ix.data_space_id=rng.data_space_id
		INNER JOIN ['+[name]+'].sys.index_columns AS ic WITH (NOLOCK) ON ix.[object_id]=ic.[object_id] AND ix.index_id=ic.index_id AND ic.partition_ordinal>0
		INNER JOIN ['+[name]+'].sys.columns AS c WITH (NOLOCK) ON ic.[object_id]=c.[object_id] AND ic.column_id=c.column_id
		LEFT  JOIN ['+[name]+'].sys.partitions AS p WITH (NOLOCK) ON ix.[object_id]=p.[object_id] AND ix.index_id=p.index_id AND rng.boundary_id=p.partition_number
		' AS sql3,

		--- Databases, schemas, tables
		'SELECT '+CAST(database_id AS nvarchar(max))+', o.[object_id], s.name+''.''+o.name
		 FROM ['+[name]+'].sys.objects AS o WITH (NOLOCK)
		 INNER JOIN ['+[name]+'].sys.schemas AS s WITH (NOLOCK) ON o.[schema_id]=s.[schema_id]
		 WHERE o.[type] NOT IN (''U'', ''S'')
		' AS sql4

	FROM sys.databases WITH (NOLOCK)
	WHERE state_desc='ONLINE' AND database_id IN (
		SELECT resource_database_id
		FROM @tran_locks);
OPEN #cur;

FETCH NEXT FROM #cur INTO @name, @sql, @sql2, @sql3, @sql4;
WHILE (@@FETCH_STATUS=0) BEGIN;
    BEGIN TRY;
    	INSERT INTO @database_objects EXEC(@sql);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/schema: '+ERROR_MESSAGE();
    END CATCH;

	BEGIN TRY;
        INSERT INTO @files EXEC(@sql2);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/database files: '+ERROR_MESSAGE();
    END CATCH;

	BEGIN TRY;
    	INSERT INTO @parts EXEC(@sql3);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/partitions: '+ERROR_MESSAGE();
    END CATCH;

	BEGIN TRY;
    	INSERT INTO @database_procedures EXEC(@sql4);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/tables: '+ERROR_MESSAGE();
    END CATCH;

	FETCH NEXT FROM #cur INTO @name, @sql, @sql2, @sql3, @sql4;
END;

CLOSE #cur;
DEALLOCATE #cur;


-------------------------------------------------------------------------------
--- Availability groups stuff:

UPDATE f SET f.primary_replica=ags.primary_replica, f.[availability_mode]=ar.[availability_mode], f.suspend_state=x.suspend_state, f.queue_kb=x.queue_kb, f.rpo=x.rpo
FROM @files AS f
INNER JOIN sys.databases AS db ON f.database_id=db.database_id
INNER JOIN sys.availability_replicas AS ar ON db.replica_id=ar.replica_id
--INNER JOIN sys.availability_groups AS ag ON ar.group_id=ag.group_id
INNER JOIN sys.dm_hadr_availability_group_states AS ags ON ar.group_id=ags.group_id
LEFT JOIN (
    SELECT database_id, group_id, (CASE MAX(suspend_reason_desc)
                WHEN 'SUSPEND_FROM_USER' THEN 'A user manually suspended data movement'
                WHEN 'SUSPEND_FROM_PARTNER' THEN 'The database replica is suspended after a forced failover'
                WHEN 'SUSPEND_FROM_REDO' THEN 'An error occurred during the redo phase'
                WHEN 'SUSPEND_FROM_APPLY' THEN 'An error occurred when writing the log to file (see error log)'
                WHEN 'SUSPEND_FROM_CAPTURE' THEN 'An error occurred while capturing log on the primary replica'
                WHEN 'SUSPEND_FROM_RESTART' THEN 'The database replica was suspended before the database was restarted (see error log)'
                WHEN 'SUSPEND_FROM_UNDO' THEN 'An error occurred during the undo phase (see error log)'
                WHEN 'SUSPEND_FROM_REVALIDATION' THEN 'Log change mismatch is detected on reconnection (see error log)'
                WHEN 'SUSPEND_FROM_XRF_UPDATE' THEN 'Unable to find the common log point (see error log)'
                ELSE MAX(suspend_reason_desc)
                END) AS suspend_state, MAX(log_send_queue_size) AS queue_kb, DATEDIFF(second, MIN(last_commit_time), MAX(last_commit_time)) AS rpo
    FROM sys.dm_hadr_database_replica_states
    GROUP BY database_id, group_id) AS x ON f.database_id=x.database_id AND ar.group_id=x.group_id;

-------------------------------------------------------------------------------
--- Beautify names of temp tables:
UPDATE @database_objects
SET [name]=SUBSTRING(LEFT([name], CHARINDEX(REPLICATE('_', 8), [name]+REPLICATE('_', 8))-1), 5, LEN([name]))
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
	REPLACE([name], '_', '.') LIKE 'dbo.#%'+REPLICATE('.', 8)+'[0-9A-F][0-9A-F]%[0-9A-F][0-9A-F]';

--- Beautify names of temp table indexes:
UPDATE @database_objects
SET index_name='Primary key'
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
	LEFT(index_name, 12) LIKE 'PK__'+LEFT([name], 8);

UPDATE @database_objects
SET index_name=REPLACE(RTRIM(REPLACE(LEFT(index_name, LEN(index_name)-16), '_', ' ')), ' ', '_')
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
	REPLACE(index_name, '_', '.') LIKE '%#%'+REPLICATE('[0-9A-F]', 16);



-------------------------------------------------------------------------------
--- Chains of spids that are being blocked, per spid:
WITH blocks (spid, blocked_by, ord)
AS (
	SELECT DISTINCT session_id AS spid, blocking_session_id AS blocked_by,
		DENSE_RANK() OVER (PARTITION BY blocking_session_id ORDER BY session_id) AS ord
	FROM @os_waiting_tasks
	WHERE blocking_session_id IS NOT NULL AND session_id!=blocking_session_id),

     list (spid, blocking, list, ord)
AS (
	SELECT blocked_by AS spid, spid AS blocking, CAST(spid AS nvarchar(max)) AS list, ord
	FROM blocks
	WHERE ord=1
	UNION ALL
	SELECT c.spid, c.blocking, CAST(c.list+', '+CAST(b.spid AS nvarchar(max)) AS nvarchar(max)) AS list, b.ord
	FROM list AS c
	INNER JOIN blocks AS b ON
		c.spid=b.blocked_by AND
		c.ord+1=b.ord)

INSERT INTO @blocking (spid, blocking)
SELECT spid, list AS blocking
FROM list AS l
WHERE ord=(SELECT MAX(ord) FROM list WHERE spid=l.spid);



--- Spids that are blocking other spids (reverse-lookup)
WITH blocks (spid, blocked_by)
AS (
	SELECT DISTINCT session_id AS spid, blocking_session_id AS blocked_by
	FROM @os_waiting_tasks
	WHERE blocking_session_id IS NOT NULL AND session_id!=blocking_session_id),

     chain (spid, blocked_by, chain, lvl)
AS (
	SELECT spid, blocked_by, CAST(blocked_by AS nvarchar(max)) AS chain, 1 AS lvl
	FROM blocks
	UNION ALL
	SELECT c.spid, b.blocked_by, CAST(c.chain+' <- '+CAST(b.blocked_by AS nvarchar(max)) AS nvarchar(max)) AS chain, lvl+1
	FROM chain AS c
	INNER JOIN blocks AS b ON b.spid=c.blocked_by)

INSERT INTO @block_chain (spid, blocked_by)
SELECT spid, MAX('<- '+chain)
FROM chain AS c
WHERE lvl=(SELECT MAX(lvl) FROM chain WHERE spid=c.spid)
GROUP BY spid;



--- Compile locks
INSERT INTO @locks
SELECT
	tl.request_session_id AS spid,
	NULLIF(tl.request_owner_id, 0) AS tran_id,
	DENSE_RANK() OVER (
		PARTITION BY tl.request_session_id, tl.request_owner_id, obj.[object_id], obj.index_id, obj.partition_number
		ORDER BY (CASE WHEN tl.request_mode='GRANT' THEN 1 ELSE 2 END), tl.request_mode
		) AS lock_ord,
	ISNULL(NULLIF(tl.request_status, 'GRANT')+' ', '')+tl.request_mode+' '+(CASE tl.resource_type
		WHEN 'DATABASE' THEN 'db'
		WHEN 'FILE' THEN 'file'
		WHEN 'OBJECT' THEN ISNULL(LOWER(REPLACE(REPLACE(REPLACE(obj.type_desc, '_', ' '), 'SQL ', ''), 'USER ', '')), 'obj')
		WHEN 'PAGE' THEN 'page'
		WHEN 'KEY' THEN 'key'
		WHEN 'EXTENT' THEN 'extent'
		WHEN 'RID' THEN 'RID'
		WHEN 'APPLICATION' THEN 'app'
		WHEN 'METADATA' THEN 'metadata'
		WHEN 'HOBT' THEN 'HoBT'
		WHEN 'ALLOCATION_UNIT' THEN 'alloc unit'
		ELSE tl.resource_type
		END) AS mode,
	db.name AS [database], ISNULL(obj.[object_id], 0), obj.name AS [object_name],
	ISNULL(obj.index_id, 0), obj.index_type, obj.index_name, obj.index_filter,
	(CASE	WHEN obj.type_desc='USER_TABLE' AND obj.is_clustered=1 THEN 'Clustered'
		WHEN obj.type_desc='USER_TABLE' THEN 'Heap'
		WHEN obj.type_desc='VIEW' AND obj.is_clustered=1 THEN 'Indexed view'
		ELSE '' END) AS object_notes,
	obj.[rows] AS obj_rows,
	ISNULL(obj.partition_number, 0), (SELECT MAX(partition_number)
			FROM @database_objects AS sub
			WHERE sub.database_id=obj.database_id AND
				sub.[object_id]=obj.[object_id] AND
				index_id=obj.index_id
			) AS partition_count,
	parts.boundary AS partition_boundary,
	(CASE tl.request_owner_type
		WHEN 'TRANSACTION' THEN 'Trans'			    --- The request is owned by a transaction.
		WHEN 'CURSOR' THEN 'Cursor'			        --- The request is owned by a cursor.
		WHEN 'SESSION' THEN 'Session'			    --- The request is owned by a user session.
		WHEN 'SHARED_TRANSACTION_WORKSPACE' THEN 'Tran ws (shared)' --- The request is owned by the shared part of the transaction workspace.
		WHEN 'EXCLUSIVE_TRANSACTION_WORKSPACE' THEN 'Tran ws (excl)' --- The request is owned by the exclusive part of the transaction workspace.
		WHEN 'NOTIFICATION_OBJECT' THEN 'Internal'	--- The request is owned by an internal SQL Server component. This component has requested the lock manager to notify it when another component is waiting to take the lock. The FileTable feature is a component that uses this value.
		END) AS request_owner_type,
	wt.wait_type,
	dbo.fn_friendly_age(DATEADD(ms, 0-wt.wait_duration_ms, GETDATE()), GETDATE()) AS wait_time,
	wt.blocking_session_id AS blkd_by_spid,
	COUNT(*) AS [count],
	(CASE WHEN tl.request_status='WAIT' THEN 1 ELSE 0 END) AS isWaiting,
	0 AS isBlocking
FROM @tran_locks AS tl
INNER HASH JOIN sys.databases AS db WITH (NOLOCK) ON tl.resource_database_id=db.database_id
INNER HASH JOIN @tran_session_transactions AS st ON tl.request_owner_id=st.transaction_id
LEFT HASH JOIN @os_waiting_tasks AS wt ON tl.lock_owner_address=wt.resource_address
LEFT HASH JOIN @database_objects AS obj ON tl.resource_database_id=obj.database_id AND tl.resource_associated_entity_id IN (obj.[object_id], obj.hobt_id)
LEFT HASH JOIN @parts AS parts ON obj.database_id=parts.database_id AND parts.hobt_id=obj.hobt_id
WHERE tl.request_session_id!=@@SPID
GROUP BY tl.request_session_id, tl.request_owner_id, tl.request_status, tl.request_mode, tl.resource_type, db.name, obj.[object_id], obj.index_id, obj.index_type,
	obj.index_name, obj.index_filter, obj.type_desc, obj.[object_id], obj.database_id, obj.is_clustered, obj.name, obj.[rows],
	obj.partition_number, parts.boundary, tl.request_owner_type, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id;


--- Update @locks.isBlocking column.
UPDATE blocking
SET blocking.isBlocking=1
FROM @locks AS blocked
INNER JOIN @locks AS blocking ON
	blocked.[database]=blocking.[database] AND
	ISNULL(blocked.[object_id], -1)=ISNULL(blocking.[object_id], -1) AND
	ISNULL(blocked.index_id, -1)=ISNULL(blocking.index_id, -1) AND
	ISNULL(blocked.partition_number, -1)=ISNULL(blocking.partition_number, -1) AND
	blocked.tran_id!=blocking.tran_id AND
	blocked.spid!=blocking.spid AND
	blocked.isWaiting=1;



SET LOCK_TIMEOUT -1;

-------------------------------------------------------------------------------
--- 1. Server properties:
-------------------------------------------------------------------------------

SELECT
	CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS nvarchar(128)) AS [Physical name],
	CAST(SERVERPROPERTY('ServerName') AS nvarchar(128)) AS [Instance name],
	(CASE CAST(SERVERPROPERTY('IsClustered') AS bit) WHEN 1 THEN ISNULL('Cluster node '+sqlsrv.cluster_nodename, 'Clustered') ELSE 'Stand-alone' END)+
    (CASE WHEN SERVERPROPERTY('IsHadrEnabled')=1 THEN ' with Availability Groups' ELSE '' END)+
	(CASE @virtual_machine_type
		WHEN 1 THEN ' on Hypervisor'
		WHEN 2 THEN ' on virtual machine'
		ELSE '' END) AS [Configuration],
	'SQL Server '+(CASE @version
		WHEN '09.0' THEN '2005'
		WHEN '10.0' THEN '2008'
		WHEN '10.5' THEN '2008 R2'
		WHEN '11.0' THEN '2012'
		WHEN '12.0' THEN '2014'
		WHEN '13.0' THEN '2016'
		WHEN '14.0' THEN '2017'
        WHEN '15.0' THEN '2019' ELSE @version END)+' '+
		REPLACE(REPLACE(REPLACE(
			REPLACE(CAST(SERVERPROPERTY('Edition') AS nvarchar(128)), (CASE WHEN @version>='13.0' THEN ' (64-bit)' ELSE '' END), ''), ' Edition', ''),
			'Standard', 'Std'), 'Enterprise', 'Ent') AS [Product, edition],
	CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(128))+ISNULL(', '+
        CAST(SERVERPROPERTY('ProductUpdateLevel') AS nvarchar(128)), '')+' ('+
		CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128))+')' AS [Level, CU],
	(CASE WHEN @max_server_memory/@mb>2000000 THEN ''
          ELSE
            (CASE WHEN @min_server_memory<@max_server_memory AND @min_server_memory>0
                THEN LTRIM(dbo.fn_friendly_size(@min_server_memory))+' - '
                ELSE '' END)+
            LTRIM(dbo.fn_friendly_size(@max_server_memory))+
		    (CASE WHEN @max_server_memory!=@physical_memory THEN ' / ' ELSE '' END) END)+
		LTRIM(dbo.fn_friendly_size(@physical_memory)) AS [Min - max / physical mem],
--	dbo.fn_friendly_size(@virtual_memory) AS [Virtual mem],
	CAST(sysinfo.cpu_count/sysinfo.hyperthread_ratio AS nvarchar(10))+
		ISNULL('x'+CAST(NULLIF(sysinfo.hyperthread_ratio, 1) AS nvarchar(10)), '')+
		ISNULL(' ('+CAST((SELECT NULLIF(MAX(memory_node_id), 0)+1
		                  FROM sys.dm_os_memory_clerks
						  WHERE memory_node_id<64) AS nvarchar(10))+' NUMA)', '') AS [Core count],
	ISNULL(CAST(NULLIF(@max_degree_of_parallelism, 0) AS nvarchar(10)), '-') AS [MaxDOP],
	dbo.fn_friendly_age(sysinfo.sqlserver_start_time, GETDATE()) AS Uptime,
	CAST(SERVERPROPERTY('Collation') AS nvarchar(128)) AS [Server collation],
    sqlsrv.service_account AS [Service acct],
--  sqlagt.service_account AS [Agent svc acct],
    sqlagt.status_desc+' ('+sqlagt.startup_type_desc+')' AS [SQL Server Agent]
FROM sys.dm_os_sys_info AS sysinfo WITH (NOLOCK)
LEFT JOIN @dm_server_services AS sqlsrv ON sqlsrv.servicename LIKE 'SQL Server (%'
LEFT JOIN @dm_server_services AS sqlagt ON sqlagt.servicename LIKE 'SQL Server Agent (%';



-------------------------------------------------------------------------------
--- 2. Display database file usages:
-------------------------------------------------------------------------------

SELECT
	db.name AS [Database],
	UPPER(LEFT(f.filetype, 1))+LOWER(SUBSTRING(f.filetype, 2, 100)) AS [Type],
    COALESCE(
        (CASE WHEN f.primary_replica=@@SERVERNAME THEN 'Primary AG replica' WHEN f.primary_replica IS NOT NULL THEN (CASE f.[availability_mode] WHEN 1 THEN 'Synchronous AG replica' WHEN 0 THEN 'Async AG replica' ELSE '?' END) END),
        LEFT(m.mirroring_role_desc, 1)+LOWER(SUBSTRING(m.mirroring_role_desc, 2, 100)+
        ', '+REPLACE(m.mirroring_state_desc, '_', ' '))+
        (CASE m.mirroring_safety_level WHEN 1 THEN ' (async)' WHEN 2 THEN '(sync)' ELSE '(unknown)' END), '') AS [Mirror/AG],
    SUBSTRING(
        ISNULL(', '+f.suspend_state, '')+
        ISNULL(', '+LTRIM(dbo.fn_friendly_size(NULLIF(f.queue_kb, 0)*1024))+' queue', '')+
        ISNULL(', '+LTRIM(dbo.fn_friendly_age('00:00:00', DATEADD(second, NULLIF(f.rpo, 0), '00:00:00')))+' RPO', ''),
        3, 1000) [AG state],
	(CASE WHEN f.filetype='LOG' AND db.log_reuse_wait_desc!='NOTHING' THEN LEFT(db.log_reuse_wait_desc, 1)+LOWER(SUBSTRING(REPLACE(db.log_reuse_wait_desc, '_', ' '), 2, 1000)) ELSE '' END) AS [Log wait],
	UPPER(LEFT(db.recovery_model_desc, 1))+LOWER(SUBSTRING(db.recovery_model_desc, 2, 100)) AS [Recovery model],
	f.name AS [File name],
	dbo.fn_friendly_size(f.size_mb*@mb) AS [Allocated size],
    ISNULL((CASE WHEN f.filetype='IN-MEMORY' THEN 'n/a'
                 WHEN f.is_percent_growth=1 THEN dbo.fn_format_number(NULLIF(f.autogrow, 0), 1)+'%'
		ELSE dbo.fn_friendly_size(NULLIF(f.autogrow, 0)*1.0*@mb) END), 'None') AS [Autogrow],
	(CASE WHEN f.max_size_mb=0 THEN ''
		WHEN f.max_size_mb IS NULL AND f.filetype='Rows' AND CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) LIKE '%Express edition%' AND @version>='10.50' THEN dbo.fn_friendly_size(10*@gb)+' (Express ed.)'
		WHEN f.max_size_mb IS NULL AND f.filetype='Rows' AND CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) LIKE '%Express edition%' THEN dbo.fn_friendly_size(4*@gb)+' (Express ed.)'
		WHEN f.max_size_mb IS NULL AND f.filetype='IN-MEMORY' THEN 'n/a'
                WHEN f.max_size_mb IS NULL THEN '('+ISNULL(f.file_system_type, 'OS')+' max)'
		WHEN f.max_size_mb=268435456/128 THEN '(Log max, 2 TB)'
		ELSE ISNULL(dbo.fn_friendly_size(f.max_size_mb*@mb), '') END) AS [Max file size],
	ISNULL(dbo.fn_friendly_size(f.used_size_mb*@mb), '') AS [File usage],
	ISNULL(dbo.fn_format_number(100.0*f.used_size_mb/f.size_mb, 1)+'%', '') AS [File usage %],
	dbo.fn_friendly_size(1.0*ISNULL(bd.[count], 0)*@mb/128) AS [Bufferpool size],
	ISNULL(STR(100.0*NULLIF(ROUND(bd.[count], -1), 0)/(SELECT NULLIF(SUM([count]), 0) FROM @dm_os_buffer_descriptors), 10, 1)+'%', '') AS [% of bufferpool],
    f.drive AS [Drive],
	ISNULL(LTRIM(dbo.fn_friendly_size(1.0*f.available_bytes))+' of '+
	       LTRIM(dbo.fn_friendly_size(1.0*f.total_bytes)), '') AS [Free disk space]
FROM @files AS f
LEFT JOIN @dm_os_buffer_descriptors AS bd ON f.database_id=bd.database_id AND f.[file_id]=bd.[file_id]
INNER JOIN master.sys.databases AS db WITH (NOLOCK) ON db.database_id=f.database_id
INNER JOIN master.sys.database_mirroring AS m WITH (NOLOCK) ON db.database_id=m.database_id
WHERE f.database_id IN (
	SELECT database_id
	FROM @exec_requests
	UNION
	SELECT DISTINCT resource_database_id
	FROM @tran_locks
	WHERE request_session_id IN (
		SELECT session_id
		FROM @exec_sessions
		WHERE login_name=SUSER_SNAME()))
ORDER BY db.name, (CASE f.filetype
    WHEN 'ROWS' THEN 0
    WHEN 'LOG' THEN 1
    WHEN 'FILESTREAM' THEN 2
    WHEN 'IN-MEMORY' THEN 3 ELSE 0 END), f.name;



-------------------------------------------------------------------------------
--- 3. Display all sessions:
-------------------------------------------------------------------------------

SELECT
	xc.session_id AS SPID,
	ISNULL('"'+job.job_name+'", step '+CAST(job.step_id AS nvarchar(max))+' "'+job.step_name+'"',
		(CASE xs.[program_name]
			WHEN 'Microsoft SQL Server Management Studio - Query' THEN 'SSMS Query'
			WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS'
			ELSE xs.[program_name]
			END)) AS [Application],
--	xs.client_interface_name AS [Interface],
--	ISNULL(NULLIF(xc.client_net_address, xc.local_net_address), 'local')+ISNULL(':'+CAST(xc.client_tcp_port AS nvarchar(max)), '') AS [Client addr],
	xs.login_name+ISNULL(' ('+xc.auth_scheme+')', '') AS [Login name (auth)],
	dbo.fn_friendly_age(xc.connect_time, GETDATE()) AS [Conn. age],
	ISNULL(dbo.fn_friendly_size(NULLIF(xs.logical_reads*8192, 0)), '') AS [Logical reads],
	ISNULL(dbo.fn_friendly_size(NULLIF(xs.reads*8192, 0)), '') AS [Reads],
	ISNULL(dbo.fn_friendly_size(NULLIF(xs.writes*8192, 0)), '') AS [Writes],
	ISNULL(dbo.fn_friendly_age(0, DATEADD(ms, NULLIF(xs.cpu_time, 0), 0)), '') AS [CPU time],
	ISNULL(NULLIF(xs.[status], 'sleeping'), '') AS [State],
	ISNULL(CAST(NULLIF(trn.[count], 0) AS nvarchar(10)), '') AS [Tran count],
	ISNULL(blocking.blocking, '') AS [Blocking],
	ISNULL(chain.blocked_by, '') AS [Blocked by],
	ISNULL(SUBSTRING(cur.x.value('.', 'nvarchar(1000)'), 3, 1000), '') AS [Cursors],
	ISNULL(CAST(NULLIF((SELECT COUNT(*)
		FROM @os_tasks AS t
		WHERE t.session_id=xc.session_id), 0) AS nvarchar(10)), '') AS [Schedulers (DOP)],
	(CASE WHEN ISNULL(trn.[count], 0)>0 AND xs.[status]='sleeping' THEN dbo.fn_friendly_age(xs.last_request_start_time, GETDATE()) ELSE '' END) AS [Abandoned?],
	xs.flags AS [Connection/ANSI flags]
FROM @exec_connections AS xc
LEFT JOIN @exec_sessions AS xs ON xc.session_id=xs.session_id
LEFT JOIN @jobs AS job ON xs.[program_name] LIKE '%'+job.search_name+'%'
LEFT JOIN @block_chain AS chain ON chain.spid=xc.session_id
LEFT JOIN @blocking AS blocking ON blocking.spid=xc.session_id
LEFT JOIN (
	SELECT session_id, COUNT(*) AS [count]
	FROM @tran_session_transactions
	GROUP BY session_id) AS trn ON xc.session_id=trn.session_id
CROSS APPLY (
	SELECT ', '+[name]
	FROM sys.dm_exec_cursors(xc.session_id)
	FOR XML PATH(''), TYPE) AS cur(x)
WHERE xc.session_id!=@@SPID
ORDER BY (CASE
	WHEN xs.[status] NOT IN ('sleeping', 'dormant') OR ISNULL(trn.[count], 0)>0 THEN 1
	ELSE 2 END), xc.session_id;



-------------------------------------------------------------------------------
--- 4. Running queries:
-------------------------------------------------------------------------------

SELECT
	xc.session_id AS SPID,
	ISNULL('"'+job.job_name+'", step '+CAST(job.step_id AS nvarchar(max))+' "'+job.step_name+'"',
		(CASE xs.[program_name]
			WHEN 'Microsoft SQL Server Management Studio - Query' THEN 'SSMS Query'
			WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS'
			ELSE xs.[program_name]
			END)) AS [Application],
	ISNULL(dbobj.name, '') AS [Proc],
	(CASE WHEN xs.[status] NOT IN ('sleeping', 'dormant') THEN (CASE WHEN cmd.[encrypted]=1
		THEN xr.command+' (encrypted)'
		ELSE COALESCE(
			SUBSTRING(cmd.[text], xr.statement_start_offset/2+1, NULLIF(xr.statement_end_offset, -1)/2-xr.statement_start_offset/2),
			cmd.[text],
			xr.command COLLATE database_default,
            '') END) ELSE '('+xs.[status]+')' END) AS [Command/T-SQL],
	(CASE WHEN xs.[status]!='sleeping' THEN dbo.fn_friendly_age(xs.last_request_start_time, GETDATE()) ELSE '' END) AS [Stmt age],
	ISNULL(dbo.fn_friendly_size(mem.max_used_mb)+(CASE WHEN mem.max_used_mb<mem.granted_mb
		THEN ' of '+LTRIM(dbo.fn_friendly_size(mem.granted_mb*@mb))
		ELSE '' END)+(CASE WHEN mem.granted_mb<mem.requested_mb
		THEN ' ('+LTRIM(dbo.fn_friendly_size(mem.requested_mb*@mb))+' requested)'
		ELSE '' END), '') AS [Max used/granted memory],
	ISNULL(dbo.fn_friendly_size(mem.used_mb), '') AS [Used mem],
	ISNULL(dbo.fn_friendly_size(NULLIF(mem.ideal_mb, mem.granted_mb)), '') AS [Ideal mem],
	ISNULL(dbo.fn_format_number(mem.est_query_cost, 2), '') AS [Est. cost],
	ISNULL(dbo.fn_format_number(NULLIF(xr.percent_complete, 0.0), 1)+'%', '') AS [Progress],
	ISNULL(dbo.fn_friendly_age(GETDATE(), DATEADD(ms, 100.0*xr.total_elapsed_time/NULLIF(xr.percent_complete, 0.0), xs.last_request_start_time)), '') AS [Est remaining],
	[plan].query_plan AS [Query plan]
FROM @exec_connections AS xc
CROSS APPLY sys.dm_exec_sql_text(xc.most_recent_sql_handle) AS cmd
LEFT JOIN @exec_sessions AS xs ON xc.session_id=xs.session_id
LEFT JOIN @jobs AS job ON xs.[program_name] LIKE '%'+job.search_name+'%'
LEFT JOIN @exec_requests AS xr ON xc.session_id=xr.session_id
OUTER APPLY sys.dm_exec_query_plan(xr.plan_handle) AS [plan]
LEFT JOIN @block_chain AS chain ON chain.spid=xc.session_id
LEFT JOIN @blocking AS blocking ON blocking.spid=xc.session_id
LEFT JOIN @exec_query_memory_grants AS mem ON xc.session_id=mem.session_id
LEFT JOIN @database_procedures AS dbobj ON dbobj.database_id=cmd.dbid AND dbobj.[object_id]=cmd.objectid
WHERE xc.session_id!=@@SPID AND (
    xs.[status]!='sleeping' OR
    xc.session_id IN (SELECT session_id FROM @tran_session_transactions))
ORDER BY xc.session_id



-------------------------------------------------------------------------------
--- 5. Display open transactions:
-------------------------------------------------------------------------------

SELECT
	st.session_id AS SPID,
	act.transaction_id AS [Transaction],
	dbo.fn_friendly_age(act.transaction_begin_time, GETDATE()) AS [Transaction age],
	REPLACE((CASE st.is_local
			WHEN 0 THEN 'Distr '
			WHEN 1 THEN 'Local ' END)+
		(CASE act.transaction_type
			WHEN 1 THEN 'r/w'
			WHEN 2 THEN 'read'
			WHEN 3 THEN 'sys'
			WHEN 4 THEN 'distr'
			END), 'Distr distr', 'Distr') AS [Transaction type],
	(CASE act.transaction_state
		WHEN 0 THEN 'None'	        --- The transaction has not been completely initialized yet.
		WHEN 1 THEN 'Init'	        --- The transaction has been initialized but has not started.
		WHEN 2 THEN 'Active'	    --- The transaction is active.
		WHEN 3 THEN 'Ended'	        --- The transaction has ended. This is used for read-only transactions.
		WHEN 4 THEN 'Distr commit'  --- The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
		WHEN 5 THEN 'Prepared'	    --- The transaction is in a prepared state and waiting resolution.
		WHEN 6 THEN 'Committed'	    --- The transaction has been committed.
		WHEN 7 THEN 'Rolling back'  --- The transaction is being rolled back.
		WHEN 8 THEN 'Rolled back'   --- The transaction has been rolled back.
		END) AS [State],
	(CASE xr.transaction_isolation_level
		WHEN 0 THEN '?'
		WHEN 1 THEN 'Read uncommitted'
		WHEN 2 THEN 'Read committed'
		WHEN 3 THEN 'Repeatable read'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
		ELSE ISNULL(CAST(xr.transaction_isolation_level AS nvarchar(10)), '')
		END) AS [Isolation level],
	ISNULL(CAST(dbo.fn_friendly_age(0, DATEADD(ms, (
		SELECT AVG(wt.wait_duration_ms)
		FROM @os_waiting_tasks AS wt
		WHERE wt.session_id=wt.blocking_session_id AND
			wt.session_id=st.session_id AND
			wt.wait_type='CXPACKET'), 0)) AS nvarchar(20)), 'None') AS [Avg cxPkt wait]
FROM @tran_session_transactions AS st
INNER JOIN @tran_active_transactions AS act ON st.transaction_id=act.transaction_id
LEFT JOIN @exec_requests AS xr ON st.session_id=xr.session_id
WHERE st.session_id!=@@SPID
ORDER BY st.session_id, act.transaction_id;



-------------------------------------------------------------------------------
--- 6. Display all locks in all sessions and databases:
-------------------------------------------------------------------------------

WITH rcte (spid, tran_id, lock_ord, mode, [count], [database], [object_id], [object_name], index_id, index_type, index_name, index_filter,
	   partition_number, partition_count, partition_boundary, obj_rows, request_owner_type, wait_type, wait_time, isBlocking, isWaiting, lock_descr)
AS (
	SELECT spid, tran_id, lock_ord, mode, [count], [database], [object_id], [object_name], index_id, index_type, index_name, index_filter,
		partition_number, partition_count, partition_boundary, obj_rows, request_owner_type, wait_type, wait_time, isBlocking, isWaiting,
		CAST((CASE WHEN mode LIKE '%table' THEN '' ELSE LTRIM(ISNULL(dbo.fn_format_number(NULLIF([count], 1), 0)+' ', '')) END)+mode AS nvarchar(max))
	FROM @locks
	WHERE lock_ord=1
	UNION ALL
	SELECT rcte.spid, rcte.tran_id, lck.lock_ord, lck.mode, lck.[count], rcte.[database], rcte.[object_id], rcte.[object_name], rcte.index_id, rcte.index_type, rcte.index_name, rcte.index_filter,
		rcte.partition_number, rcte.partition_count, rcte.partition_boundary, rcte.obj_rows, rcte.request_owner_type, rcte.wait_type, rcte.wait_time,
		CAST((CASE WHEN 1 IN (rcte.isBlocking, lck.isBlocking) THEN 1 ELSE 0 END) AS bit),
		CAST((CASE WHEN 1 IN (rcte.isWaiting, lck.isWaiting) THEN 1 ELSE 0 END) AS bit),
		CAST(rcte.lock_descr+', '+(CASE WHEN lck.mode LIKE '%table' THEN '' ELSE LTRIM(ISNULL(dbo.fn_format_number(NULLIF(lck.[count], 1), 0)+' ', '')) END)+lck.mode AS nvarchar(max))
	FROM @locks AS lck
	INNER JOIN rcte ON
		rcte.lock_ord+1=lck.lock_ord AND
		rcte.spid=lck.spid AND
		rcte.tran_id=lck.tran_id AND
		rcte.[database]=lck.[database] AND
		rcte.[object_id]=lck.[object_id] AND
		ISNULL(rcte.index_id, -1)=ISNULL(lck.index_id, -1) AND
		rcte.partition_number=lck.partition_number)

SELECT spid AS SPID, tran_id AS [Transaction], lock_descr AS [Lock types],
	[database] AS [Database],
	ISNULL([object_name], '') AS [Object],
	ISNULL((CASE
		WHEN index_name='Primary key' AND index_id=1 THEN 'Primary key, clustered'
		WHEN index_type=5 THEN index_name+' (clustered colstore)'
		WHEN index_type=6 THEN index_name+' (colstore)'
		WHEN index_type=7 THEN index_name+' (hash)'
		WHEN index_id=1 THEN index_name+' (clustered)'
		WHEN index_id=0 OR index_id IS NULL THEN index_name+' (heap)' ELSE index_name END)+ISNULL(' '+index_filter, ''), '') AS [Index],
	ISNULL(STR(NULLIF(partition_number, 0), 4, 0)+'/'+CAST(NULLIF(partition_count, 1) AS nvarchar(10)), '') AS [Part'n],
	ISNULL(partition_boundary, '') AS [..bounds],
	ISNULL(dbo.fn_format_number(obj_rows, 0), '') AS [Est. object rows],
	request_owner_type AS [Req/trans owned by],
	ISNULL((CASE wait_type
		WHEN 'LCK_M_SCH_S' THEN 'Schema stability'
		WHEN 'LCK_M_SCH_M' THEN 'Schema modification'
		WHEN 'LCK_M_S' THEN	    'Share'
		WHEN 'LCK_M_U' THEN	    'Update'
		WHEN 'LCK_M_X' THEN	    'Exclusive'
		WHEN 'LCK_M_IS' THEN    'Intent-Share'
		WHEN 'LCK_M_IU' THEN    'Intent-Update'
		WHEN 'LCK_M_IX' THEN    'Intent-Exclusive'
		WHEN 'LCK_M_SIU' THEN   'Shared intent to update'
		WHEN 'LCK_M_SIX' THEN   'Share-Intent-Exclusive'
		WHEN 'LCK_M_UIX' THEN   'Update-Intent-Exclusive'
		WHEN 'LCK_M_BU' THEN    'Bulk Update'
		WHEN 'LCK_M_RS_S' THEN  'Range-share-share'
		WHEN 'LCK_M_RS_U' THEN  'Range-share-Update'
		WHEN 'LCK_M_RI_NL' THEN 'Range-Insert-NULL'
		WHEN 'LCK_M_RI_S' THEN  'Range-Insert-Shared'
		WHEN 'LCK_M_RI_U' THEN  'Range-Insert-Update'
		WHEN 'LCK_M_RI_X' THEN  'Range-Insert-Exclusive'
		WHEN 'LCK_M_RX_S' THEN  'Range-exclusive-Shared'
		WHEN 'LCK_M_RX_U' THEN  'Range-exclusive-update'
		WHEN 'LCK_M_RX_X' THEN  'Range-exclusive-exclusive'
		ELSE wait_type END), '') AS [Wait type],
	ISNULL(wait_time, '') AS [Wait time],
	(CASE WHEN isBlocking=1 THEN 'Blocking' ELSE '' END)+
	(CASE WHEN isBlocking=1 AND isWaiting=1 THEN ', ' ELSE '' END)+
	(CASE WHEN isWaiting=1 THEN 'Blocked' ELSE '' END) AS [Blocking/blocked]
FROM rcte
WHERE lock_ord=(SELECT MAX(lock_ord) FROM rcte AS x
		WHERE x.spid=rcte.spid AND x.tran_id=rcte.tran_id AND
			x.[database]=rcte.[database] AND
			ISNULL(x.[object_id], -1)=ISNULL(rcte.[object_id], -1) AND
			ISNULL(x.index_id, -1)=ISNULL(rcte.index_id, -1) AND
			ISNULL(x.partition_number, -1)=ISNULL(rcte.partition_number, -1))
ORDER BY spid, tran_id, [database], [object_name], index_id, partition_number, mode;



-------------------------------------------------------------------------------
--- Clean up:
-------------------------------------------------------------------------------

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_size'))
	DROP FUNCTION dbo.fn_friendly_size;

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_format_number'))
	DROP FUNCTION dbo.fn_format_number;

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_age'))
	DROP FUNCTION dbo.fn_friendly_age;




-------------------------------------------------------------------------------
--- CATCH
-------------------------------------------------------------------------------

END TRY
BEGIN CATCH;

	IF (EXISTS (SELECT session_id FROM sys.dm_exec_cursors(@@SPID))) BEGIN;
		CLOSE #cur;
		DEALLOCATE #cur;
	END;

	THROW;

	IF (@@TRANCOUNT!=0)
		ROLLBACK TRANSACTION;
	RETURN;

END CATCH;

ROLLBACK TRANSACTION;