SQL Server can return unused database-file space to the operating system after a substantial, permanent reduction in stored data. Shrinking is an occasional maintenance action, rather than a routine way to improve performance.
Plan the operation
Confirm that the free space is no longer needed, take a suitable backup and arrange a maintenance window. Shrinking can move data pages, increase index fragmentation and interfere with other activity.
Keep the database’s required recovery model. If only the transaction log is oversized, investigate log reuse and use a file-specific procedure instead of shrinking the entire database.
Shrink through Management Studio
- Connect to the correct SQL Server instance in SQL Server Management Studio.
- Right-click the intended database and select Tasks > Shrink > Database.
- Review the allocated and available space. Choose an appropriate free-space target for future use.
- Confirm the operation and review any messages.
For greater control over a single file, use Tasks > Shrink > Files.
Equivalent SQL operation
The following example requests that ExampleDatabase retain approximately 10% free space. Replace the database name and select a target suitable for your workload:
DBCC SHRINKDATABASE ([ExampleDatabase], 10);The number is a percentage of free space, not a size in megabytes. SQL Server cannot shrink below applicable file and used-space limits, so the requested result is not guaranteed.
Check the result
Review the file sizes, query performance, fragmentation and subsequent file growth. Do not schedule repeated shrinking. If normal activity immediately needs the space again, allocate suitable capacity rather than repeatedly shrinking and regrowing it.