How to shrink SQL Server Database in 4 easy steps
Step 1) Look at file size
Before actually thinking about shrinking a file, look at file size and how much free space there is. Run this in the database you want to shrink data files for:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT LogicalName = dbf.name ,FileType = dbf.type_desc ,FilegroupName = fg.name ,PhysicalFileLocation = dbf.physical_name ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0) ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0)) ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)FROM sys.database_files dbfLEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_idORDER BY dbf.type DESC, dbf.name; |
If you look at the FreeSpaceMB column in your results, that is the amount of unused space you might potentially be able to reclaim. If it’s significant, you might decide to move on and shrink the files.
Keep in mind that you want set all files for a given filegroup at the same size. When you let file sizes within a filegroup get out of alignment, SQL’s proportional fill algorithm won’t spread IO evenly across files.
Step 2) Consider the side effects
Yes, you’re still trying to talk yourself out of doing this shrink.
If you shrink the data file, any data located at the “end” of the file needs to be related elsewhere in the file. These operations are logged, so you’re going to generate a lot of transaction log usage. Make sure your transaction log is big enough, and make sure you are running transaction log backups frequently enough.
If your database is being mirrored, or is in an Availability Group, or is log shipping, you might find that your secondary server(s) fall behind during this operation.
Shrinking is going to create a lot of fragmentation. Depending on your workload, you might have a performance impact during the process.
Shrinking generates a bunch of IO. If you are already seeing IO issues, that might be exacerbated by shrinking, and you might have performance impact during the process.
Shrinking is a mostly online process, where data is moved page-by-page to the “front” of the file so that the end of the file can be truncated. Say mostly online because its possible for this operation to cause blocking–we have seen this particularly often in the PRIMARY filegroup. Because of the possibility of this causing blocking, Never let it run unattended, and never during peak business hours.
Be really sure that you need to shrink.
Step 3) Shrink files
Recommended to use DBCC SHRINKFILE(BOL) when shrink, never DBCC SHRINKDATABASE. If going to shrink a file, must have as much control over the process as possible.
Using the output from the above SELECT, figure out what you want to use for a target size on your database–You’ll want to leave some free space in your data file, otherwise it’s just going to grow bigger right away.
Now you can build the statement(s) to shrink your data file. Remember, this is an online process, but if you’re worried about impacting performance, watch your server carefully while this runs:
|
1
2
|
USE [DatabaseName];DBCC SHRINKFILE(LogicalName, TargetSize); |
Step 4) Review fragmentation
Congratulations, you’ve successfully created a ton of physical fragmentation. If fragmentation does affect your performance, then you already have a regularly scheduled index maintenance job. After shrinking the data file, run that regular index maintenance job to get things squared away.