question

gotqn avatar image
gotqn asked

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?
datashrink-database
10 |1200 characters needed characters left characters exceeded

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

Grant Fritchey avatar image
Grant Fritchey answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
"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 Likes 0 ·
Thanks for the reply. I have done further more tests and reading and created a new topic question here.
0 Likes 0 ·
pvc avatar image
pvc answered
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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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