How to shrink a database in 4 easy steps

It’s a really common question: “How do I shrink my database?”
Luckily, there’s also a really common answer: “Don’t.”

…but what if you really have to?

Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.

I’m not even going to talk about log files…that’s a totally different topic. We’re just talking about data files. In this post, I’ll focus on a method that is both online, and works in all editions of SQL Server.

Step 0) Don’t shrink

Before you get to step 1, convince yourself that you really have to shrink your database. I’ll let Brent Ozar (blog|twitter) tell you to Stop Shrinking Your Database Files. Seriously. Now.

If you’re convinced you need to shrink, and you’re not being stupid, then let’s try to do it without causing too much trouble.

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:

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 dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER 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. I say mostly online because its possible for this operation to cause blocking–I’ve seen this particularly often in the PRIMARY filegroup. Because of the possibility of this causing blocking, I never let it run unattended, and never during peak business hours.

Be really sure that you need to shrink.

Step 3) Shrink files

I always use DBCC SHRINKFILE(BOL) when I shrink, never DBCC SHRINKDATABASE. If I am going to shrink a file, I want 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:

USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Step 4) Review fragmentation

Congratulations, you’ve successfully created a ton of physical fragmentation. I know some shops run applications where fragmentation just doesn’t cause any problems. If you’re one of those shops, then you’re done–stop reading.

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.

If you don’t have a regular index maintenance job, and think you need one: figure out what the best options are, and if you even need to consider it. Kendra Little (blog|twitter) gives a good intro to the topic here. I personally like Ola Hallengren’s IndexOptimize procedure.

 

But can I do all of this online?

Remember: Even if you’re running Standard Edition, REORGANIZE is an online operation. Better yet, if you kill it you won’t lose your work–it will pick up where it left off the next time.

You can use DBCC SHRINKFILE and REORGANIZE to do this completely online. If the shrink causes massive fragmentation that impacts production, you can shrink/reorganize/shrink/reorganize in smaller chunks. This will take a really, really long time. But that’s a trade-off… There are faster methods, but those will likely involve varying amounts of downtime.

The easiest solution is to just leave the database half-full and accept that you’re using more disk space than you need to. Not shrinking your database at all is always an online operation.