By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,503 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

Reclain space after DROP COLUMN

P: n/a
I have a huge table in it own tablespace (250 GB). I just deleted more than
half of the columns in that table, but I didn't see any change in allocated
space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also says
that CLEANTABLE only reclaims space for columns with text, char datatypes.
How can I get space back for integer, decimal, number datatypes? I'm just
all out of ideas.

Jul 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Ansti (an***@hot.ee) writes:
I have a huge table in it own tablespace (250 GB). I just deleted more
than half of the columns in that table, but I didn't see any change in
allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing.
MS also says that CLEANTABLE only reclaims space for columns with text,
char datatypes. How can I get space back for integer, decimal, number
datatypes? I'm just all out of ideas.


A DBCC DBREINDEX on the table could be a good thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
Yes, DBCC CLEANTABLE only works on variable lengthed columns. To reclaim
space from dropped fixed length column, if the base table is an clustered
index, use DBCC DBREINDEX; if the base table is a heap, first build a
clustered index on it then drop the index to get a new heap.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Ansti" <an***@hot.ee> wrote in message
news:42*********************@diablo.uninet.ee...
I have a huge table in it own tablespace (250 GB). I just deleted more than half of the columns in that table, but I didn't see any change in allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also says that CLEANTABLE only reclaims space for columns with text, char datatypes.
How can I get space back for integer, decimal, number datatypes? I'm just
all out of ideas.

Jul 23 '05 #3

P: n/a
I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Ansti (an***@hot.ee) writes:
I have a huge table in it own tablespace (250 GB). I just deleted more
than half of the columns in that table, but I didn't see any change in
allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing.
MS also says that CLEANTABLE only reclaims space for columns with text,
char datatypes. How can I get space back for integer, decimal, number
datatypes? I'm just all out of ideas.


A DBCC DBREINDEX on the table could be a good thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #4

P: n/a
DBCC INDEXDEFRAG won't be any help here as it doesn't remove space from
individual records. You best bet is to rebuild the clustered index using
DBCC DBREINDEX.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Ansti" <an***@hot.ee> wrote in message
news:42*********************@diablo.uninet.ee...
I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Ansti (an***@hot.ee) writes:
I have a huge table in it own tablespace (250 GB). I just deleted more
than half of the columns in that table, but I didn't see any change in
allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also says that CLEANTABLE only reclaims space for columns with text,
char datatypes. How can I get space back for integer, decimal, number
datatypes? I'm just all out of ideas.


A DBCC DBREINDEX on the table could be a good thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Jul 23 '05 #5

P: n/a
I see. Thanks a lot!

"Paul S Randal [MS]" <pr*****@online.microsoft.com> wrote in message
news:42********@news.microsoft.com...
DBCC INDEXDEFRAG won't be any help here as it doesn't remove space from
individual records. You best bet is to rebuild the clustered index using
DBCC DBREINDEX.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no
rights.

"Ansti" <an***@hot.ee> wrote in message
news:42*********************@diablo.uninet.ee...
I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
> Ansti (an***@hot.ee) writes:
>> I have a huge table in it own tablespace (250 GB). I just deleted more
>> than half of the columns in that table, but I didn't see any change in
>> allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. >> MS also says that CLEANTABLE only reclaims space for columns with
>> text,
>> char datatypes. How can I get space back for integer, decimal, number
>> datatypes? I'm just all out of ideas.
>
> A DBCC DBREINDEX on the table could be a good thing.
>
>
> --
> Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp



Jul 23 '05 #6

P: n/a
It seems DBCC INDEXDEFRAG requires quite much disk space. I on ly have ~2 GB
free. How about if I delete clustered index and create it again. Would it
work and with only 2 GB of free space???
"Paul S Randal [MS]" <pr*****@online.microsoft.com> wrote in message
news:42********@news.microsoft.com...
DBCC INDEXDEFRAG won't be any help here as it doesn't remove space from
individual records. You best bet is to rebuild the clustered index using
DBCC DBREINDEX.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no
rights.

"Ansti" <an***@hot.ee> wrote in message
news:42*********************@diablo.uninet.ee...
I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
> Ansti (an***@hot.ee) writes:
>> I have a huge table in it own tablespace (250 GB). I just deleted more
>> than half of the columns in that table, but I didn't see any change in
>> allocated space. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. >> MS also says that CLEANTABLE only reclaims space for columns with
>> text,
>> char datatypes. How can I get space back for integer, decimal, number
>> datatypes? I'm just all out of ideas.
>
> A DBCC DBREINDEX on the table could be a good thing.
>
>
> --
> Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp



Jul 23 '05 #7

P: n/a
Ansti (an***@hot.ee) writes:
It seems DBCC INDEXDEFRAG requires quite much disk space. I on ly have
~2 GB free. How about if I delete clustered index and create it again.
Would it work and with only 2 GB of free space???


Hm, the entire table was 250 GB, and you have 2GB free? That looks
problematic. You may have to see your local hardware vendor.

Dropping the clustered index means that the table will become a heap and
to achieve this all non-clustered must be rebuilt. (Since the data-page
pointers in the NC indexes is the clustered index keys.) And the to
recreate the clustered index, you need the space for the new index as
well as the heap.

In any case, Paul's reply was that INDEXDEFRAG would not help you, but
that you should use DBREINDEX. Unfortunately, also a DBREINDEX for a
250 GB table is likely to require a lot more free disk than 2GB.

Your best bet in this situation may be to find another disk and then
do "CREATE CLUSTERED INDEX ... ON [NEWDISK] WITH DROP_EXISTING". This
will effectively move clustered index (and thus the data) to the
new disk. I would expect it to compact the table at the same time.

Please see Books Online for the exact syntax of the command.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.