Unused space in a table

Question

Unused space in a table

Vets5

SSC Eights!

Points: 982
More actions
July 9, 2014 at 4:05 am

#309875

Hello all,
Following a large cleanse of data I now have a lot of unused space in a table. The problem I have is this is not reflected in the free space in the data files(as the space is allocated), I have no desire to shrink the data files I just want space to be held with in the mdf/ndf files or for the unused space to be reused. I believe the issue I am seeing is because of an image column within the table. I need to retrieve the space to allow new rows be created in the table in question.
Unfortunately a database redesign is not an option. Does anyone have any suggestions on how I can reclaim the space? I seen suggestions on copying the column to a temporary DB, dropping the column and reimporting the data. Unfortunately the data in this table is quite substantial so I'm looking for alternatives(if they even exist!)
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Try DBCC UPDATEUSAGE and see what things look like then.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

Vets5 (7/9/2014)
Hello all,
Following a large cleanse of data I now have a lot of unused space in a table. The problem I have is this is not reflected in the free space in the data files(as the space is allocated), I have no desire to shrink the data files I just want space to be held with in the mdf/ndf files or for the unused space to be reused. I believe the issue I am seeing is because of an image column within the table. I need to retrieve the space to allow new rows be created in the table in question.
Unfortunately a database redesign is not an option. Does anyone have any suggestions on how I can reclaim the space? I seen suggestions on copying the column to a temporary DB, dropping the column and reimporting the data. Unfortunately the data in this table is quite substantial so I'm looking for alternatives(if they even exist!)
Thanks!

It sounds like all you need to do is a DBCC CLEANTABLE followed by a rebuild the indexes... especially the clustered indexes.

It would also be helpful if we knew what you meant by "Following a large cleanse of data I now have a lot of unused space in a table...". What did this consist of... DELETE's, TRUNCATES, SWITCH outs, dropping columns, some combination of that?

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

Vets5 SSC Eights! Points: 982 More actions · Answer 3

Thank you both for your response.

I've attempted the DBCC UPDATEUSAGE however I have not seen anything improvement.

Jeff,

We have carried out a large amount of deletes on the database only. I will look at the DBCC CLEANTABLE now and report back if I have success.

Thank you kindly for you help