How to reduce unallocated space?

Question

question

gotqn asked Jul 30, '14

How to reduce unallocated space?

I have executed `sp_spaceused` and get the following data: database_name database_size unallocated space test 482767.63 MB 166888.50 MB reserved data index_size unused 198876288 KB 158468464 KB 39772752 KB 635072 KB So, I have a lot of unallocated space and have read that it could be reduce by using `DBCC SHRINKFILE`. I have read that `unallocated space is space that is not reserved for use either by data or log files (Space Available)`. So, should I reduce it at first place and why it is so large?

data shrink-database

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-07-30T13:54:07Z

Grant Fritchey answered Jul 30, '14

You should always have some unallocated space within your database. Otherwise, as you need to add data, the database will have to grow the space to accommodate, slowing things down. I wouldn't suggest messing with it too much. Shrinking files can lead to serious fragmentation issues at the OS level that are not easily fixed.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gotqn commented · Jul 31, 2014 at 06:46 AM

So, this is normal behaviour? Aren't 168 GB too much free space for 482 GB database? Also, could you tell if shrinking the database with 10% percent free spaces means that after these 10% are filled with data, no inserts will be performed.

0 ·

Grant Fritchey ♦♦ commented · Jul 31, 2014 at 10:08 AM

"Normal" is a pretty tough definition in this field. It's not radically abnormal, I can tell you that. As you delete data, the size of the files doesn't decrease, so it's entirely possible to have grown it to a certain size and then have some of that be empty later. Not knowing the full extent and history of the database and just a little information, it sounds perfectly fine. Whether or not you get inserts depends on whether or not you have auto-growth enabled. If it's disabled and you fill up the remaining space, yes, all inserts stop.

0 ·

gotqn commented · Jul 31, 2014 at 12:58 PM

Thanks for the reply. I have done further more tests and reading and created a new topic question here.

0 ·

Answer 2 · 2014-07-31T08:25:27Z

pvc answered Jul 31, '14

1st of all. Identify you you **really** need to shrink that database. Are you running out of disk space ? If not, you shouldn't shrink. Shrinking causes fagmentation to the database. Read this article from the SQL Guru Paul Randal: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ or that from Brent Ozar: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question