How to reclaim space from a table

Question

How to reclaim space from a table

WhiteLotus

SSChampion

Points: 12122
More actions
January 18, 2022 at 5:06 am

#3974101

Hi All
I have 1 TB database and i cant shrink it as it shows zero % free space ..
when I checked into the report of disk usage by table .. there is 1 table which has unused space 700 GB . This table has no Clustered index and only has non clustered index
every night we perform Index maintenance on that database
How Do I reclaim the space ..
Your feedback is much appreciated
Thank you
January 19, 2022 at 2:58 am

This was removed by the editor as SPAM
February 22, 2022 at 12:23 am

This was removed by the editor as SPAM
March 22, 2022 at 6:47 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

You need to put yourself a clustered index on the table. Shrinking heaps is very trick why put yourself through the pain.

Alternatively pull out all the data left, truncate, repopulate.

If your deleting from the heap use TABLOCK hint.

If all that fails you can try a manual REBUILD on the table but by default nothing will automatically rebuild a heap.

Michael L John One Orange Chip Points: 27055 More actions · Answer 2

If you have zero free space, you may not be able to put a clustered index on the table.

I suggest you add space to the file or filegroup, and attempt to rebuild the heap. If you can create enough space, create a clustered index.

OR

Create another filegroup, and duplicate the table on the new file/filegroup. Use the SWITCH command to move the data from the old to the new table.

ALTER TABLE schema.OldTable SWITCH TO schema.NewTable

Rebuild the heap, or add a clustered index to the new table

Then, switch back.

This reply was modified 2 years, 10 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

WhiteLotus SSChampion Points: 12122 More actions · Answer 3

Thanks for the reply all . Much appreciated ..

tonight i will try alter table rebuild and see how it goes

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

What "index maintenance" do you perform on the heap?!

I think a REBUILD should release unused space from a heap. REBUILD is just:

ALTER TABLE <your_table_name_here> REBUILD;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Adow1946 Newbie Points: 3 More actions · Answer 5

Adow1946

Newbie

Points: 3

January 25, 2022 at 12:44 pm

#3976942

Thanks!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Michael L John wrote:

.....
Create another filegroup, and duplicate the table on the new file/filegroup. Use the SWITCH command to move the data from the old to the new table.
ALTER TABLE schema.OldTable SWITCH TO schema.NewTable
Rebuild the heap, or add a clustered index to the new table
Then, switch back.

"Both the source table or partition, and the target table or partition, must be located in the same filegroup. "

ref: "Alter Table"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Before you go rebuilding the table to recover the space, at least find out why there's so much unused space in the table. Is it due to forwarded rows or something else like, maybe, that bloody "Fast Insert" problem that creates a new extent for just one row if you're inserting into the table in a necessarily RBAR fashion.

Taking corrective action without knowing the cause isn't a "fix".

--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: 1004323 More actions · Answer 8

WhiteLotus wrote:

Thanks for the reply all . Much appreciated ..
tonight i will try alter table rebuild and see how it goes

So... how'd it go?

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 9

I'd clean up the prod problem first. You can use a backup and research to research the other stuff, if you think it's necessary.

Also, once you get the table reduced in size, you should be able to add a clustered index which should help prevent issues for the future. I wouldn't spend time researching something now that won't be an issue in the future.

If you think you have the extent issue, that should how up again relatively quickly. You can deal with it then.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

ScottPletcher wrote:

I'd clean up the prod problem first. You can use a backup and research to research the other stuff, if you think it's necessary.

Only if you have a place large enough to do a restore. 😉

It only takes 10 minutes to do the checks and can take an hour to do a backup and another hour to do a restore. Just do it while it's on your mind so you don't forget to actually fix the original problem before it becomes a problem again in the future.

--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: 1004323 More actions · Answer 11

WhiteLotus wrote:

every night we perform Index maintenance on that database

As a bit of a sidebar, I can almost guarantee that you're doing you index maintenance wrong. Spend more time doing integrity checks and rebuilding stats, instead.

Now. shifting gears back to the problem at hand, have you been able to resolve your problem with this database? If not, post back. And, yeah... you're going to need some working free-space on a different drive to fix this if the disk is full.

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