0

I'm trying to free up some space in a database, firstly by swapping out and archiving partitions. The DB is massive though, and i want to start freeing up some space on the drive, but I don't want to completely shrink the DB as it will take weeks.

Is it possible to shrink a SQL Server DB by say 10gb?

Thanks all!

CM

marc_s
  • 9,052
  • 6
  • 46
  • 52
CatchingMonkey
  • 253
  • 4
  • 9

2 Answers2

5

Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Why Does the Transaction Log Keep Growing or Run Out of Space?

Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.

If you want to shrink the data file by 10GB, look at the size it is now, and then use DBCC SHRINKFILE (not DBCC SHRINKDATABASE) and set a size 10GB smaller than that. Look at the size column from sp_helpfile - let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:

DBCC SHRINKFILE(filename, 31000); -- 31,000 MB

You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
0

The database cannot be made smaller than the minimum size of the database.The minimum size is the size specified when the database was originally created or set by using a file-size-changing operation.Have a look with below query 

SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;

For details about current amount of free (unallocated) space in the database check this

Is it possible to shrink an MSSQL DB by say 10gb?

All these can be used to estimate how much GB DataBase can shrink.

  • Minimum DB size
  • Unallocated DB space
  • Existing DB size and
  • How much Percentage you would like to keep as free space

In case if I would like to keep 10% of free space

DBCC SHRINKDATABASE (UserDB, 10);

Check DBCC SHRINKDATABASE for more details

Note:- shrinking Database is not advisable

Edit : Even in case you really sure to shrink the database

the option for SHRINKFILE is better as it will shrink a single file while SHRINKDATABASE will shrink all the files in database and you may never need to shrink the entire database.

SHRINKFILE gives the option to set the output size of target file, below target DataFileName is set to shrink to a size of 8 MB.

DBCC SHRINKFILE (DataFileName, 8);

Check this link

vijayp
  • 2,786
  • 1
  • 17
  • 20