When executing DBCC SHRINKFILE on large databases (especially 600GB+ files), you might encounter the operation showing as "Suspended" in sys.dm_exec_requests while percent_complete continues increasing slowly. This typically indicates resource contention or internal SQL Server throttling mechanisms.

SELECT 
    session_id, wait_type, wait_time, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE session_id = YOUR_DBCC_SESSION_ID;

The key wait types to look for:

• PAGEIOLATCH_*: Indicates disk I/O bottlenecks
• RESOURCE_SEMAPHORE: Memory pressure
• LCK_M_*: Blocking from other transactions

Enhanced monitoring script:

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    t.text AS sql_text,
    r.cpu_time,
    r.total_elapsed_time,
    r.percent_complete,
    r.estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%DBCC%' OR r.command LIKE '%SHRINK%';

For I/O bottlenecks:

-- Run during maintenance window
DBCC SHRINKFILE (N'YourDataFile' , target_size_in_MB)
WITH NO_INFOMSGS, TABLOCK;

For memory pressure:

-- Pre-allocate memory
DBCC MEMORYSTATUS;
-- Consider increasing max server memory
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', NEW_VALUE_MB;
RECONFIGURE;

For very large files, consider:

1. Creating a new filegroup and migrating data
2. Using partition switching
3. Performing the shrink in smaller batches

Example batch approach:

WHILE EXISTS (SELECT 1 FROM sys.database_files WHERE name = 'YourFile' AND size > target_size)
BEGIN
    DBCC SHRINKFILE (N'YourFile', target_size);
    WAITFOR DELAY '00:05:00'; -- Pause between attempts
END

When running DBCC SHRINKFILE on large databases (like your 600GB file), the DbccFilesCompact operation may enter a "Suspended" state. This typically indicates resource contention or throttling within SQL Server.

• Resource Pressure: Memory, I/O, or CPU bottlenecks
• Blocking Transactions: Other processes modifying the database
• Log File Growth: Insufficient log space during operation
• Lock Contention: Schema locks preventing progress
• System Load: High concurrent workload on the server

This enhanced version of your query provides more diagnostic information:

SELECT 
    T.text AS [SQL Text],
    R.Status,
    R.Command,
    DB_NAME(R.database_id) AS DatabaseName,
    R.cpu_time,
    R.total_elapsed_time,
    R.percent_complete,
    R.wait_type,
    R.wait_time,
    R.wait_resource,
    R.blocking_session_id,
    R.logical_reads,
    R.reads,
    R.writes
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE R.Command = 'DbccFilesCompact'
ORDER BY R.total_elapsed_time DESC;

Check these additional DMVs to identify the specific bottleneck:

-- Check for blocking processes
SELECT * FROM sys.dm_os_waiting_tasks 
WHERE session_id IN (SELECT session_id FROM sys.dm_exec_requests WHERE Command = 'DbccFilesCompact');

-- Check I/O subsystem performance
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    vfs.file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall,
    num_of_reads,
    num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs;

For large files, consider these approaches:

1. Batch Processing: Shrink in smaller chunks

DBCC SHRINKFILE (N'YourDataFile' , target_size_in_mb);
GO 10 -- Run 10 times with decreasing target sizes

2. Maintenance Window: Run during low-activity periods
3. Pre-Operation Cleanup: Rebuild indexes first

-- Rebuild all indexes before shrinking
EXEC sp_MSforeachtable @command1="PRINT '?' DBCC DBREINDEX ('?', ' ', 80)";

For very large databases, consider:

-- Create new filegroup and move data
ALTER DATABASE YourDB ADD FILEGROUP NEW_FG;
ALTER DATABASE YourDB ADD FILE (NAME='YourDB_New', FILENAME='E:\Data\YourDB_New.ndf') TO FILEGROUP NEW_FG;

-- Move data to new filegroup
-- (Implement appropriate data movement strategy here)

-- Then remove the old file

Use this extended query to track progress over time:

WITH ShrinkStats AS (
    SELECT 
        R.session_id,
        DB_NAME(R.database_id) AS db_name,
        R.start_time,
        R.percent_complete,
        R.estimated_completion_time/1000 AS est_sec_remaining,
        R.wait_type,
        R.wait_time,
        GETDATE() AS snapshot_time
    FROM sys.dm_exec_requests R
    WHERE R.Command = 'DbccFilesCompact'
)
INSERT INTO ShrinkMonitorLog
SELECT * FROM ShrinkStats;