This question already has an answer here:

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

marked as duplicate by sp_BlitzErik, LowlyDBA, Tom V, mustaccio, Joe Obbish Apr 11 '17 at 4:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Are you saying the db will grow to use the free 60GB again within 3 months? – Gareth Lyons Apr 10 '17 at 13:32
  • @GarethLyons "The database will not grow upto 60GB" – SqlWorldWide Apr 10 '17 at 13:33
  • 1
    @SqlWorldWide "in at least 3 months" if it's going to use some (or most) of that empty 60GB in 3-6 months, I personally wouldn't bother shrinking the file. – Gareth Lyons Apr 10 '17 at 13:38
  • 2
    @GarethLyons totally agree, me neither. but that is up to him/her if he/she needs the space for now for something else. I do not know. – SqlWorldWide Apr 10 '17 at 13:40
  • @SqlWorldWide indeed. – Gareth Lyons Apr 10 '17 at 13:41

What exactly you are trying to achieve by shrinking the database? You should plan for the database growth and have reserved space for this growth.

So why don't you leave it as it is to have allocated space for new data coming to your DB.

It has been discussed so many times that you do not want to shrink your databases. Check this out and think twice before shrinking:

Stop Shrinking Your Database Files. Seriously. Now. By Brent Ozar

  • 1
    Every once in a while, you do need to shrink. It should be rare, but sometimes you gotta do it. I think it's more constructive to help the OP determine whether this is one of those rare occasions, rather than simply saying "never do it" ... because, like everything, It Depends. – AMtwo Apr 10 '17 at 15:23
  • We had a DB go from 10GB to 10MB once. – Joshua Apr 10 '17 at 23:26
  • +1 The better solution is to add disk space if you need it. 3 months is nothing. – jpmc26 Apr 11 '17 at 0:50

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.

Use this code below. Details here. 7 is in MB.

USE UserDB;  
GO  
DBCC SHRINKFILE (DataFile1, 7);  
GO  
  • Leave some empty space though, as otherwise the index rebuilds will just grow the file again. – Gareth Lyons Apr 10 '17 at 13:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.