Shrink File or Shrink Database

  • Hello,

    I am shrinking DB of size 150 GB.

    I ran Shrink file command (through GUI) and it was quick, say a minute.

    However, when I run Shrink Database command, it takes a lot of time (half an hour to an hour, easily).

    I see that about 10 GB space will will be there on the disk after I run Shrink Database, which was not the free after I ran Shrink file command.

    May I know the difference between the two ?

    Thanks in advance!

  • Shrinkdatabase shrinks all the files in the database, shrinkfile shrinks only the specified file.

    Why do you want to shrink anyway? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the reply Coach.

    Well, I have to shrink the files since there were huge number of inserts and deletes performed.

    "Shrinkdatabase shrinks all the files in the database, shrinkfile shrinks only the specified file."

    I am not an expert at this, however, I feel that it is more than that since I ran shrink database file and log file and

    some space was released. And after I ran shrink database, I see good amount of (further) space being free.

    There is something which is missing. I googled but did not find anything so here I am, asking the same thing to experts.

    Can you tell me, why did the database shrink further and also shrink database takes unexpectedly longer than the shrink file command.

  • touchmeknot (6/14/2010)


    Well, I have to shrink the files since there were huge number of inserts and deletes performed.

    Why? SQL will reuse the space. Only shrink if the free space will not be reused in a long period of time

    Can you tell me, why did the database shrink further and also shrink database takes unexpectedly longer than the shrink file command.

    Could be timing, especially if that free space came from the log.

    Do note that you should be rebuilding all your indexes about now, as well as fixing the size of the tran log (assuming that you shrunk both to minimum)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I too was wondering the real difference between Shrinking files vs database. Management Studio has an option for each but when choosing shrink file, I don't see where you choose which file.

    Physical documents were mistakenly saved to the database instead of to a server directory due to an ini setting in a software application. Over a period of 5 months the database grew to over 6G when it only needs to be 2G at the most. Being that I am going to now send a backup to the developers via the internet I am going to shrink the database. Those files will never again be saved as records in the database.

    See there really are good reasons to shrink.

  • Ken.Cushing (2/27/2014)


    I too was wondering the real difference between Shrinking files vs database. Management Studio has an option for each but when choosing shrink file, I don't see where you choose which file.

    First drop down selects whether it's a log file or data file you want to shrink. Second dropdown selects which filegroup the file you want to shrink is in (if data file), the third selects which file in that filegroup you want to shrink

    See there really are good reasons to shrink.

    Never said there weren't.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply