SQL queries to troubleshoot a long-running DBCC SHRINK operation

Find problematic objects that may slow down SHRINK operations.sql

/*
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-18
Description:
  This script will detect tables in your database that may cause DBCC SHRINK operations
  to run really slow:
    - Tables with LOB_DATA or ROW_OVERFLOW_DATA
    - Heap tables with non-clustered indexes
    - Heap tables with partitions
  
  You may adjust the @TableSizeThresholdMB parameter to filter the tables based on their size.
*/
DECLARE
	@TableSizeThresholdMB INT = 500


;WITH TabsCTE
AS
(
SELECT  DISTINCT
	'Table with LOB or ROW-OVERFLOW data' AS Issue,
	p.object_id
FROM    sys.system_internals_allocation_units au
JOIN    sys.partitions p ON au.container_id = p.partition_id
WHERE type_desc <> 'IN_ROW_DATA' AND total_pages > 8
AND p.rows > 0

UNION ALL

SELECT
	'Heap with Non-clustered indexes',
	p.object_id
FROM	sys.partitions AS p
WHERE	p.index_id = 0
AND p.rows > 0
AND EXISTS (SELECT NULL FROM sys.indexes AS ncix WHERE ncix.object_id = p.object_id AND ncix.index_id > 1)

UNION ALL

SELECT DISTINCT
	'Partitioned Heap',
	p.object_id
FROM	sys.partitions AS p
WHERE	p.index_id = 0
AND p.rows > 0
AND p.partition_number > 1
)
SELECT t.*,
OBJECT_SCHEMA_NAME(t.object_id) table_schema,
OBJECT_NAME(t.object_id) table_name,
SUM(p.rows) AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM TabsCTE AS t
INNER JOIN sys.partitions p ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id 
GROUP BY t.Issue, t.object_id
HAVING SUM(a.used_pages) / 128.00 >= @TableSizeThresholdMB
ORDER BY Used_MB DESC

Find SHRINK sessions with lock info.sql

/*
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-18
Description:
  This script will detect currently running sessions in your database which are running DBCC SHRINK commands.
  It will also output the name of any tables and indexes the session is currently locking.
  
  Use this query to find out what causes a SHRINK to run for too long.
  You may need to run it multiple times to "catch" the relevant info.
  Optionally, set @RunUntilCaughtLockInfo to 1 to continuously run until a session with object lock info was caught.
*/
DECLARE @RunUntilCaughtLockInfo BIT = 0

DECLARE @Results TABLE
(
	session_id int null,
	start_time datetime null,
	command nvarchar(max) null,
	[status] sysname null,
	wait_time int null,
	wait_type sysname null,
	[dbid] int null,
	dbname sysname null,
	[objid] int null,
	SchemaName sysname null,
	TableName sysname null,
	IndexId int null,
	IndexName sysname null
);

WHILE 1=1
BEGIN

	INSERT INTO @Results
	SELECT DISTINCT
		req.session_id,
		req.start_time,
		req.command,
		req.status,
		req.wait_time,
		req.wait_type,
		ISNULL(rsc_dbid, req.database_id) AS dbid,
		DB_NAME(ISNULL(rsc_dbid, req.database_id)) AS dbname,
		rsc_objid AS ObjId,
		OBJECT_SCHEMA_NAME(rsc_objid, rsc_dbid) AS SchemaName,
		OBJECT_NAME(rsc_objid, rsc_dbid) AS TableName,
		rsc_indid As IndexId,
		indexes.name AS IndexName
	FROM sys.dm_exec_requests AS req
	LEFT JOIN master.dbo.syslockinfo ON req_spid = req.session_id AND rsc_objid <> 0
	LEFT JOIN sys.indexes ON syslockinfo.rsc_objid = indexes.object_id AND syslockinfo.rsc_indid = indexes.index_id
	WHERE req.command IN ('DbccFilesCompact', 'DbccSpaceReclaim')
	OPTION(RECOMPILE);

	IF @@ROWCOUNT = 0 AND @RunUntilCaughtLockInfo = 1 CONTINUE;
	IF @RunUntilCaughtLockInfo = 0 BREAK;

	IF NOT EXISTS (SELECT * FROM @Results WHERE [objid] IS NOT NULL)
	BEGIN
		DELETE @Results;
		CONTINUE;
	END
	ELSE
	BEGIN
		BREAK;
	END

END

SELECT *
FROM @Results;