Reduce MDF file size

April 1, 2016 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data.

I tried re indexing, it’s good the database has de-fragmented. But the DB size is not reduced.

I am not prepared to use shrink database. I have already tried compression and DBCC UpdateUsage but no space gain. Please advise me any other ways to reduce the database size.

Our client will not allow us to take the DB Offline. It might take 5 hr to shrink the database and then re indexing will take 3 hrs, so in total we would need 8 hr down time. I am looking for a solution without downtime.

asked 2016-04-01 by kannadhasan G

Answer

Step 0) Shrink disclaimer

I’m not even going to say it. I’ll let Brent Ozar tell you to Stop Shrinking Your Database Files. Seriously. Now.

In your comments, you mention you deleted 50% of the data in the database, so you very likely have a good use case for a one-time database shrink. (Assuming that the deleted data isn’t going to come back.)

I’m not even going to touch on shrinking logs. We’re just talking data files.

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.

Step 2) Consider the side effects

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 (Unless your database is in SIMPLE recovery). 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 yoru 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 thing you need one: figure out what the best options are, and if you even need to consider it. Kendra Little gives a good intro to the topic here. I personally like Ola Hallengren’s IndexOptimize procedure.

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

It can be done completely online
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.

answered 2016-04-06 by Andy Mallon