Database Shrink in SQL Server

Database Shrink in SQL Server

Database Shrinking is the process of reclaiming unused space in a SQL Server database by reducing the physical size of data and log files. While shrinking can free up disk space, it should be used cautiously because it can lead to fragmentation and performance issues.

Why Use Database Shrinking?

? Reclaims unused space after large deletions.

? Reduces storage costs for databases with excessive free space.

? Can be useful for development/testing environments where space needs to be managed actively.

1. When to Use Database Shrink?

? After large data deletions: When a significant amount of data has been deleted.

? Freeing up disk space: If the database has a large amount of unused space.

? Non-production environments: Useful for development or test databases.

? Avoid Frequent Shrinking! Shrinking can cause index fragmentation, leading to performance degradation.

2. Shrinking a Database

?? Using T-SQL (DBCC SHRINKDATABASE)

DBCC SHRINKDATABASE (YourDatabaseName, TargetPercentage);        

• TargetPercentage: Percentage of free space to leave in the database.

Example:

DBCC SHRINKDATABASE (AdventureWorks, 10);        

This will shrink the AdventureWorks database and leave 10% free space.

3. Shrinking a Specific File

?? Using T-SQL (DBCC SHRINKFILE)

DBCC SHRINKFILE (FileName, TargetSizeInMB);        

• FileName: The logical name of the file.
• TargetSizeInMB: The desired file size in MB.

Example:

DBCC SHRINKFILE (AdventureWorks_Log, 100);        

This shrinks the transaction log file of the AdventureWorks database to 100 MB.

4. How to Check File Sizes?

SELECT name, size * 8 / 1024 AS SizeMB 
FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');        

5. Best Practices for Shrinking a Database

? Avoid frequent shrinking – it causes fragmentation.

? Rebuild indexes after shrinking to improve performance.

? Monitor database growth instead of shrinking frequently.

? Use shrinking only when necessary – like after archiving large amounts of data.

https://handbookofsuresh.blogspot.com/2025/02/database-shrink-in-sql-server.html

?Previous Article - Ledger Tables for Immutable Data in SQL Server 2022