File size alone does not tell you how much allocated data capacity is in use. Oversized data files deserve a capacity review when the workload will not need that space again. Measure size and internal use first, then decide whether a one-time shrink is worth its movement and fragmentation costs.
Measure Oversized Data Files in Their Own Database
sys.database_files reports the current database's file definitions. FILEPROPERTY with SpaceUsed reports page allocation inside a data file. Compare those values in the same database context. A logical file name from another database does not identify the intended file for this function.
I check the context before comparing file sizes. An instance-wide file list paired with a database-local FILEPROPERTY call can produce NULL or misleading results. The next query focuses on ROWS data files and leaves unsupported or unavailable use values visible.
Size and used pages describe file-level capacity, not a count of live business-row bytes. Indexes, allocation structures, and reserved space participate in that accounting. The internal free portion can be reused by SQL Server without growing the file. A large file with space available is not automatically an emergency. The file has paid for its room; the question is whether you still need the room.
SELECT name,physical_name,size/128.0 AS SizeMB,
FILEPROPERTY(name,'SpaceUsed')/128.0 AS UsedMB,
(size-FILEPROPERTY(name,'SpaceUsed'))/128.0 AS FreeMB,
100.0*(size-FILEPROPERTY(name,'SpaceUsed'))/NULLIF(size,0) AS FreePct
FROM sys.database_files WHERE type_desc='ROWS'
ORDER BY FreeMB DESC;Review oversized data files against internal free pages and the future workload before choosing a smaller physical file target.
Distinguish Internal Free Space From Volume Space
Free pages inside a database file remain part of the file's filesystem allocation. They are available to the database but not necessarily to another application on the volume. Volume free space and internal file free space therefore answer different capacity questions.
FILEPROPERTY can return NULL when the requested property is unavailable. Do not replace that with zero and report the whole file as unused. Verify the logical name, current database, file type, and supported interpretation. Missing accounting is a collection issue, not a shrink recommendation.
I compare the result with recent purge and growth history. A large permanent archive removal can leave capacity the database genuinely no longer needs. A recurring monthly load can need the same capacity again next week. Those two patterns look similar in one snapshot but deserve opposite decisions. Keep the forecast and observed history beside the current free percentage before classifying the file as oversized.
Keep Normal Growth Out of a Shrink Cycle
Repeated shrinking followed by regrowth moves pages, fragments rowstore indexes, and creates extra allocation work. If the workload will refill the space, retaining that capacity is usually the sensible operating choice. A nightly shrink job does not fix a recurring capacity requirement.
The next query shows the configured growth settings with current file sizes. Percentage and page-based growth have different units, so the output labels their interpretation explicitly. Review growth increments together with expected workload peaks and storage capacity.
What event permanently reduced the future space requirement? Identify that event before planning a shrink. Without a durable reduction, the operation simply exchanges reusable internal space for future growth work. Do not set the target equal to today's allocated use with no margin. The file still needs capacity for normal changes, maintenance, and the next planned workload window. A target is a forecast decision, not just subtraction.
SELECT name,size/128.0 AS SizeMB,is_percent_growth,growth,
CASE WHEN is_percent_growth=1 THEN CONVERT(nvarchar(30),growth)+N' percent'
ELSE CONVERT(nvarchar(30),growth/128.0)+N' MB' END AS GrowthSetting,
max_size
FROM sys.database_files WHERE type_desc='ROWS';
Use a Reviewed Target and Low-Priority Wait
SQL Server 2022 adds low-priority waiting support for shrink operations. The example below targets one logical data file and uses ABORT_AFTER_WAIT equal to SELF, so the shrink abandons the relevant wait rather than terminating blocking sessions. Replace the file name and target with reviewed values.
The target size is a demonstration setting, not a measured available or recommended size for your database. Choose it using actual use, future demand, and the file's supported minimum behavior. A requested target does not guarantee the file can reach it under every layout and workload condition.
Plan the operation during an approved window and watch blocking and IO. Low-priority waiting helps with a specific contention behavior; it does not remove the operation's page movement or make it free. Keep the shrink scoped to the file whose long-term excess capacity has been established. Do not shrink every file because one purge created a convincing percentage.
DBCC SHRINKFILE(N'ReviewedDataFile',4096)
WITH WAIT_AT_LOW_PRIORITY(ABORT_AFTER_WAIT=SELF);Recheck File Size and Affected Index Layout
After the operation, rerun the file-space query and compare the actual size and free capacity. Keep the observed result rather than claiming the requested target was achieved automatically. Also verify the application workload and remaining space margin.
Shrink can fragment rowstore indexes as pages move. The next query provides a scoped index-layout inspection pattern. On a large database, narrow the object and index scope to the affected workload rather than running every physical-stats check repeatedly.
Do not immediately rebuild every index as an automatic ritual. That work can require additional space and make the file grow again. Inspect the affected access paths and determine which maintenance is actually justified. The capacity decision and subsequent index work need a combined resource plan. Otherwise, the shrink and rebuild can undo each other's intended space outcome while creating a long maintenance window.
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,OBJECT_NAME(p.object_id) AS TableName,
p.index_id,p.page_count,p.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS p
JOIN sys.indexes AS i ON i.object_id=p.object_id AND i.index_id=p.index_id
WHERE i.type IN(1,2) AND p.alloc_unit_type_desc='IN_ROW_DATA'
ORDER BY p.page_count DESC;Confirm the Capacity After Fixing Oversized Data Files
Watch the file over a representative workload period after the one-time change. If it immediately regrows, revisit the forecast rather than scheduling another shrink. Retain the reason for the operation and the measured before-and-after file evidence.
Separate data-file review from transaction-log sizing. Log capacity follows its own reuse and backup conditions, so the data-file percentage query is not a complete log-management plan. Keep each file's purpose in the decision.
Oversized data files justify action when a permanent reduction leaves capacity the database no longer needs. Measure internal use, compare future demand, and shrink only a reviewed file with an appropriate margin. Validate the actual result and affected indexes, then let the remaining capacity serve the workload instead of entering a shrink-and-grow loop.
Related reading on this blog: Manage Database Size with DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY and Available Free Space in Data and Log File.
Empty space inside a file is not automatically wasted space, it is reusable capacity whose future need matters.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
