Release a large amount of free space from a data file.

Question

Release a large amount of free space from a data file.

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 17, 2014 at 10:00 am

#397362

I have a Database that is currently 642 GB is size with 317 GB free (49%).
I do not know how it got so large but what would be the best course of action that would minimize the probability of having an adverse impact on performance?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 11 (of 11 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Do you need the space? You might need it in the future for DB growth so unless you really need it right now, you shouldn't try to release it. It can be done, but I don't recommend it so I won't post anything to do that.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Greg Goss SSCrazy Points: 2160 More actions · Answer 2

Personally, the first thing I'd do is find out why it grew like that. Was a large table deleted? Indexes changed, removed? Are there tables in there that are only populated during a bulk insert and then deleted? There might be a good reason for it to be that large, in which case you wouldn't want to reclaim that space.

If you don't feel like going through that process or otherwise determine you actually want to reclaim the space, then I'd schedule a SHRINK on the data file during a maintenance window.

-G

Miles Neale SSChampion Points: 13147 More actions · Answer 3

WC - are you concerned about the current size with the 49% free and you are thinking of shrinking it, or are you thinking about how it might grow in the future with the same percentage of free space?

Also, you should be asking the question of the growth rate of the portion of the allotment that is used and how that is growing. If the growth rate is as anticipated then there is no worry. If the growth far out paces the anticipated growth rate, you might look at why.

Reason I ask is due to systems that allow for the upload of any number of documents into the system and storing them in a database. If you anticipated that you would have two or three uploads per thousand transactions, and after review you find you are getting on average one and a half attachments per transaction you might rethink something or alter your expectations.

Not all gray hairs are Dinosaurs!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Greg A Goss (9/17/2014)
Personally, the first thing I'd do is find out why it grew like that. Was a large table deleted? Indexes changed, removed? Are there tables in there that are only populated during a bulk insert and then deleted? There might be a good reason for it to be that large, in which case you wouldn't want to reclaim that space.
If you don't feel like going through that process or otherwise determine you actually want to reclaim the space, then I'd schedule a SHRINK on the data file during a maintenance window.
-G

It may have been caused by re-indexing but I'm not sure.

49% free space is quite large and I do not see any reason why it would need to have that much free space.

The question was what would be the best option?

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Greg Goss SSCrazy Points: 2160 More actions · Answer 5

You're not going to reduce the size of the MDF without a SHRINK operation. And on a data file, that can (and most likely will) cause performance degradation, therefore...I generally do these types of tasks during a scheduled maintenance window or when users are not in whatever application uses the database.

-G

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I'm not concerned that it may grow at the same rate.

I am concerned that it happened.

I want to re-index and I thought that it would not hurt to reduce the unused space at the same time.

Edit: Considering that the size of the mdf is so large if I shrink to 25% unused space I do not see how I would experience performance issues.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

This Post is closed. I will research the issue.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

Welsh Corgi (9/17/2014)
I have a Database that is currently 642 GB is size with 317 GB free (49%).
I do not know how it got so large but what would be the best course of action that would minimize the probability of having an adverse impact on performance?

There's just not much that can be done without having a fair bit of impact on performance during any kind of repair operations.

That being said, what is the size of the 3 or 4 largest tables and their indexes? There's a trick I do with partitioned tables that might help in this case. Nope... not suggesting partitioning but it may be that a similar trick to what I use on my partitioned tables might help on your non-partitioned table(s).

Of course, if you can afford to have the empty space and you don't mind the larger footprint during a restore, the best thing to do would be to just leave it. I know... bugs me, too, but sometimes it's best to just leave it alone. Chances are that an index rebuild was responsible and that's going to happen again, someday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

Welsh Corgi (9/17/2014)
I'm not concerned that it may grow at the same rate.
I am concerned that it happened.
I want to re-index and I thought that it would not hurt to reduce the unused space at the same time.
Edit: Considering that the size of the mdf is so large if I shrink to 25% unused space I do not see how I would experience performance issues.
Thank you.

It depends on how you do the shrink. If you actually do a shrink, it's going to cause a huge amount of fragmentation because of the way they wrote shrink to work. If you just do a "release freespace", it won't cause fragmentation and will run very quickly but it will only release freespace from the "end" of the file. If there's data at the "end" of the file, it won't won't release any freespace at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Thank you so much Jeff.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 11

So let me know about the size of the 3 or 4 largest tables and their indexes. We might be able to pull off a small miracle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)