[1] states:
- "When data is deleted from a heap, the data on the page is not compressed (reclaimed). And should all of the rows of a heap page are deleted, often the entire page cannot be reclaimed"
- "The ALTER INDEX rebuild and reorganize options cannot be used to defragment and reclaim space in a heap (but they can used to defragment non-clustered indexes on a heap).
If you want to defragment a heap in SQL Server 2005, you have three options:
- 1) create a clustered index on the heap, then drop the clustered index;
- 2) Use SELECT INTO to copy the old table to a new table; or
- 3) use BCP or SSIS to move the data from the old table to a new table.
In SQL Server 2008, the ALTER TABLE command has been changed so that it now has the ability to rebuild heap"
Plz explain me:
What are the difference between compression, (de)fragmentation, reclaiming the space, shrinkfile and shrinkdatabase in MS SQL Server 2005?
What does shrinkfile and shrinkdatabase accomplish in MS SQL Server 2005?
Update:
The question was inspired by discussion in [2] - how to shrink database in MS SQL Server 2005?
Update2: @PerformanceDBA,
Congats! You've gained over 500+ in just a week. This is remarkable!
Your diagram
Thanks, once more, for your time.
I shall ask later and not here.
Internals is not my primary preoccupation and not easiest one.
It is very succinct and generally odes not invoke any doubts or questions.
I'd prefer some tool, descriptions/instructions, technique around which to develop my doubts, question and discussion.
Plz see, for ex., my questions:
- http://www.sqlservercentral.com/Forums/Topic1013693-373-2.aspx#bm1014385
- http://www.sqlservercentral.com/Forums/Topic1013975-373-1.aspx
They are basically duplicates of what I asked but cannot discuss in stackoverflow.com
Update3: @PerformanceDBA,
thanks, once more, the main purpose of my questions was to determine the ways how to resolve concrete questions (basing on as well as avoiding what ) having contradictory docs, articles, discussions, answers, etc. which you helped to detect.
Currently I do not have further (unresolvable and blocking me) questions in this area.
[1]
Brad McGehee. Brad's Sure Guide to Indexes
(11 June 2009)
http://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/
[2]
Answers and feedback to question
"Shrink a database below its initial size"
Shrink a database below its initial size