Releasing free space in mdf file to OS

April 10, 2017 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.

Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at least 3 months. So can we shrink the data file.

Am aware of fragmentation issue. I can rebuild my index since our’s is not a 24*7 database.

Please advice on shrinking MDF file

asked 2017-04-10 by Pரதீப்

Answer

Please read my post on how to shrink a database, which I’ll summarize here:

Look at your data file size

Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small. Personally, I’d account for at least 6 months growth in my target size.

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;

Consider the side effects

It sounds like you’ve already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.

Shrink your database

Always use SHRINKFILE and never SHRINKDATABASE. Use the info you determined in step 1 to build your SHRINKFILE statement(s).

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

Review fragmentation & reorganize indexes

If you have a regular maintenance job, just kick off that job and have it do it’s magic. It’s going to take a much longer time than normal, because everything will be really fragmented. You’ll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.

answered 2017-04-10 by Andy Mallon