Shrink does not release space

Question

Shrink does not release space

Viewing 14 posts - 16 through 28 (of 28 total)

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

kevaburg SSCoach Points: 18131 More actions · Answer 1

Thanks for that Erland but I do have a question.

Rebuilding an index will of course remove fragmentation and allow a database to be shrunk. That much is clear.

But if I have 200GB of data that has been removed from a database and that 200GB is spread over multiple tables in such a way that no table is fragmented more than 30% and is containing > 1000 pages then surely the reorganisation is the most helpful. It will remove the free space that is found within the data portion of the table and move it to the end so that when the SHRINKFILE with TRUNCATEONLY is performed the total size can be significantly reduced.

I still can't help but feel that a REBUILD is not necessarily the answer.

Regards,

Kev

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

REORGANIZE essentially performs a bubble-sort of the index. The pages are not changed, so if a page is only 50 % full, it will remain 50% full. Possibly, REORGANIZE could shuffle pages around so that if there are two extents with four pages in each, it could place the eight pages in a single extent and thus free up that extent. But I don't think that happens, though. Paul Randall's blog should have that information.

However, if you specify the option LOB_COMPACTION, you can regain space for LOB data, so under these circumstances, REORGANIZE can reclaim space.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

Erland Sommarskog (8/19/2013)
REORGANIZE essentially performs a bubble-sort of the index. The pages are not changed, so if a page is only 50 % full, it will remain 50% full.

I'm pretty sure that's not true. From BOL:

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.

--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)

kevaburg SSCoach Points: 18131 More actions · Answer 4

I was beginning to think I had to rethink everything I thought I knew about indexes.......

😀

Super Cat SSCertifiable Points: 7332 More actions · Answer 5

Can't believe no one has pointed out that is not a good idea to shrink Production DBs.

Super Cat SSCertifiable Points: 7332 More actions · Answer 6

Can't believe no one has pointed out that is not a good idea to shrink Production DBs.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

Talib123 (8/19/2013)
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.

It is not good to shrink without rebuild.

If you shrink and rebuild after then it's ok.

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

Super Cat SSCertifiable Points: 7332 More actions · Answer 8

There is a lot more work needed if this is to be done properly.

Shrinking the data file is simply a bad idea and a simple index rebuild is not sufficient.

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

from Paul Randall.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

IgorMi (8/19/2013)
It is not good to shrink without rebuild.
If you shrink and rebuild after then it's ok.

No, it may be OK. In this case, the jfgrocha removed 200 GB of data, and if he believes that in the future they will have a archiving process running regularly, shrinking the database is OK. But this is just a one-off, the next archiving will happen in two years, shrinking is useless. And in any case, any shrink should leave headroom for spaces needed until next archiving operation.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

Erland Sommarskog (8/19/2013)
IgorMi (8/19/2013)
It is not good to shrink without rebuild.
If you shrink and rebuild after then it's ok.
No, it may be OK. In this case, the jfgrocha removed 200 GB of data, and if he believes that in the future they will have a archiving process running regularly, shrinking the database is OK. But this is just a one-off, the next archiving will happen in two years, shrinking is useless. And in any case, any shrink should leave headroom for spaces needed until next archiving operation.

Yes, and I read the Paul's blog from above.

It sometimes could cause still problems even if you'd rebuild the indexes.

Thanks, this thread is good

IgorMi

Igor Micev,My blog: www.igormicev.com

kevaburg SSCoach Points: 18131 More actions · Answer 11

Talib123 (8/19/2013)
Can't believe no one has pointed out that is not a good idea to shrink Production DBs.

I am not sure it can be said it isn't a good idea.....but I think most could agree it is (most of the time) pointless. The tables and datafiles are that big for a reason!

But as Long as the SHRINK is done correctly (meaning performing the REBUILD afterwards) then there shouldn't be a Problem.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 12

Sometimes a database file shrink is indeed needed.

Shrink does not automatically fragment every index. Absolutely you should test for fragmentation after the shrink, and rebuild accordingly, i.e., basically run your normal rebuild process. [A reorg won't help a hugely fragmented index nearly as much as a full rebuild.]

The SORT_IN_TEMPDB rebuild option seems to me to be overlooked in this discussion. I would strongly recommend it in this situation, since you're trying to reduce the size of the main database. Of course you first have to be sure that the free tempdb data space is large enough to hold the index you're rebuilding. In fact, if necessary, add disk to the tempdb as needed to handle the rebuilds, then remove it when the rebuilds are done (hopefully there's a LUN(s) that can be "borrowed" temporarily for this purpose if required).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 13

Data compression should also be considered, if on Enterprise Edition. [My company has some extremely large audit/logging tables that are rarely read. After compressing them, I often shrink out the freed space, then rebuild all tables except the huge compressed ones. Since they are very rarely read, (extent) fragmentation is not a significant concern for those tables.]

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 14

Jeff Moden (8/19/2013)
I'm pretty sure that's not true. From BOL:
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.

I read about it Kalen Delaney's SQL Server Internals 2008, and as I understand it, some compaction can occur, but only if can be performed over adjacent pages. You certainly get a better effect with REBUILD.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]