How to Reduce MDF File Size in SQL Server

April 7, 2025

Introduction

SQL Server databases can grow significantly over time, leading to large MDF (primary database) files. An oversized MDF file can impact performance, storage costs, and backup times. In this guide, we’ll explore practical steps to analyze, clean up, and shrink your MDF file without affecting database integrity.

Step 1: Identify Large Tables

Before shrinking the MDF file, first, analyze which tables are consuming the most space. Run the following query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    i.index_id <= 1  -- Only heap (0) or clustered index (1)
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC;

This helps you identify the biggest tables in your database so you can decide what to clean up.

Step 2: Delete Unused Data

If your database contains old or unnecessary records, consider deleting them. However, deleting in large batches is recommended to avoid table locks.

Delete old records (in small batches, if required)

SET ROWCOUNT 10000;  -- Deletes in batches of 10,000
DELETE FROM TableName WHERE CreatedDate < '2023-01-01';
SET ROWCOUNT 0;  -- Reset batch size

Archive old data instead of deleting

Instead of deleting data, consider moving it to an archive table:

SELECT * INTO ArchiveTable FROM TableName WHERE CreatedDate < '2023-01-01';
DELETE FROM TableName WHERE CreatedDate < '2023-01-01';

Step 3: Rebuild Indexes to Free Up Space

After deleting a large number of rows, the space used by indexes remains allocated. To reclaim it, rebuild or reorganize indexes:

Rebuild indexes (best for heavy fragmentation)

ALTER INDEX ALL ON TableName REBUILD;

Reorganize indexes (if fragmentation is low

ALTER INDEX ALL ON TableName REORGANIZE;

Step 4: Shrink the MDF File

Once you have cleaned up data and rebuilt indexes, you can shrink the database file.

Shrink the entire database

DBCC SHRINKDATABASE (YourDatabaseName);

⚠ Important Notes:

Shrinking the database can cause fragmentation.
Only shrink if absolutely necessary, and avoid doing it frequently.
If your MDF file is still large after shrinking, consider rebuilding indexes first.

Conclusion

Enable Table Partitioning (For Large Databases)

If you frequently deal with millions of records, consider partitioning large tables to improve query performance and reduce file size.

intertoons

Interoons aim at providing electronically intelligent and comprehensive range of digital marketing solutions that exceed customer expectations. We implement revolutionary digital marketing ideas to achieve a common as well as the aggregate growth of the organization. Long-term customer relations and extended support are maintained.