When performing database maintenance in SQL Server, DBCC SHRINKFILE is a crucial command for reclaiming unused space. However, unlike some other maintenance operations, it doesn't provide a built-in progress indicator. Here's how to effectively monitor its progress across SQL Server 2005, 2008, and newer versions.

The most reliable way to track SHRINKFILE progress is through the DMVs (Dynamic Management Views). This query provides comprehensive information about the ongoing operation:

SELECT 
    T.text AS [CommandText],
    R.Status,
    R.Command,
    DB_NAME(R.database_id) AS DatabaseName,
    R.cpu_time AS [CPU Time (ms)],
    R.total_elapsed_time AS [Elapsed Time (ms)],
    R.percent_complete AS [Progress (%)]
FROM 
    sys.dm_exec_requests R
    CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE 
    R.command LIKE '%SHRINK%' OR 
    T.text LIKE '%SHRINKFILE%'

• percent_complete: Shows completion percentage (when available)
• total_elapsed_time: Helps estimate remaining time
• command: Verifies the exact operation being performed

For environments prior to SQL Server 2008 where percent_complete might not be available:

-- Check file size changes over time
SELECT 
    name AS [FileName],
    size/128.0 AS [CurrentSizeMB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [AvailableSpaceMB]
FROM 
    sys.database_files
WHERE 
    name = 'main_data'

Remember that SHRINKFILE operations can be resource-intensive. For large databases, consider:

• Running during maintenance windows
• Breaking into smaller chunks with TRUNCATEONLY
• Monitoring log file growth during the operation

Here's a complete script with error handling for safe monitoring:

BEGIN TRY
    -- Initiate shrink operation
    DBCC SHRINKFILE('main_data', 250000) WITH NO_INFOMSGS;
    
    -- Monitoring loop
    WHILE EXISTS (
        SELECT 1 FROM sys.dm_exec_requests 
        WHERE command LIKE '%SHRINK%'
    )
    BEGIN
        SELECT 
            GETDATE() AS [Timestamp],
            DB_NAME(database_id) AS DatabaseName,
            percent_complete,
            DATEDIFF(MINUTE, start_time, GETDATE()) AS [Duration (minutes)]
        FROM 
            sys.dm_exec_requests
        WHERE 
            command LIKE '%SHRINK%'
        
        WAITFOR DELAY '00:01:00'; -- Check every minute
    END
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

When running DBCC SHRINKFILE operations on large databases in SQL Server 2005/2008, administrators often need visibility into the progress. The command itself doesn't provide built-in progress reporting, but we can leverage system DMVs to monitor execution.

This query provides the most comprehensive view of ongoing shrink operations:

SELECT 
    T.text AS [CommandText],
    R.status AS [Status],
    R.command AS [CommandType],
    DB_NAME(R.database_id) AS [DatabaseName],
    R.cpu_time AS [CPUms],
    R.total_elapsed_time AS [Durationms],
    R.percent_complete AS [Progress%],
    R.estimated_completion_time AS [ETAms],
    R.wait_type AS [CurrentWait]
FROM 
    sys.dm_exec_requests R
CROSS APPLY 
    sys.dm_exec_sql_text(R.sql_handle) T
WHERE 
    R.command LIKE '%SHRINK%' OR T.text LIKE '%SHRINK%'

• percent_complete: Shows completion percentage (0-100)
• estimated_completion_time: Milliseconds remaining (divide by 60000 for minutes)
• wait_type: Reveals if the process is blocked (e.g., PAGEIOLATCH)

For SQL Server 2005 where some DMVs might be limited:

SELECT 
    session_id,
    start_time,
    status,
    command,
    percent_complete,
    DATEADD(ms,estimated_completion_time,GETDATE()) AS estimated_end_time
FROM 
    sys.dm_exec_requests
WHERE 
    command LIKE '%DBCC%' 
    AND command LIKE '%SHRINK%'

Remember that SHRINKFILE operations can be resource-intensive:

• Monitor disk queue length during operation
• Consider running during maintenance windows
• For very large files, batch the operation into smaller chunks

For multi-terabyte databases, this approach minimizes impact:

-- Initial shrink target
DBCC SHRINKFILE('main_data', 300000) WITH NO_INFOMSGS;

-- Check progress
WAITFOR DELAY '00:05:00'; -- Wait 5 minutes
-- Run monitoring query above

-- Subsequent smaller shrink
DBCC SHRINKFILE('main_data', 290000) WITH NO_INFOMSGS;