Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active July 8, 2023 06:32
Show Gist options
  • Save EitanBlumin/037cf3b55b94413e7eb479af43cf8281 to your computer and use it in GitHub Desktop.
Save EitanBlumin/037cf3b55b94413e7eb479af43cf8281 to your computer and use it in GitHub Desktop.
SQL queries to troubleshoot a long-running DBCC SHRINK operation
/*
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
/*
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment