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.
7  4722 
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
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.
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: James Knowlton |
last post by:
Hello,
I'm just returning to MS SQL Server after two years of dealing with
Sybase ASE. I need to drop a column, using the alter table command.
I keep getting an error indicating that a...
|
by: Robert Stearns |
last post by:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the...
|
by: Chris Cobb |
last post by:
I have a table that currently contains 718000 rows. I wish to add a
column to the table. Adding this column anywhere other than the end of
the table requires exporting data, a drop and recreate,...
|
by: Yasaswi Pulavarti |
last post by:
does a command like,
db2 drop table tabschema.tabname when run from the Aix prompt reclaim
the disk space? Are there any other options? How can we make sure the
disk space is reclaimed?
Thanks,...
|
by: John Sidney-Woollett |
last post by:
Is it possible to alter a table to resize a varchar column? The PG docs
indicate lots of uses for "alter table T alter column C..." but not one
that allows the changing of the type (or same type,...
|
by: Ying Lu |
last post by:
Hi,
I have a question about alter a column's type in a postgreSQL table.
For example, I have 10, 000 records in a table name "test", I'd like to
change column "machineID" type from integer to...
|
by: Markus |
last post by:
I tried this:
ALTER TABLE Dokumente
DROP COLUMN docPrioID
but I get this errormessage:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE
Dokumente
DROP;CONSTRAINT
|
by: Giacomo |
last post by:
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:
...
|
by: Gladiator |
last post by:
Hi Team ,
Can some one tell me why we dont we have a DROP column or rename a
column command in DB2 ? I Have a very Huge table and need to rename a
column . The only option i have to do to rename...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
