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  4752 
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****@sommarsk og.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******** *************@d iablo.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****@sommars kog.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****@sommarsk og.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******** *************@d iablo.uninet.ee ... I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommars kog.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****@sommarsk og.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******** *************@d iablo.uninet.ee ... I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommars kog.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****@sommarsk og.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******** *************@d iablo.uninet.ee ... I friend of mine also suggested DBCC INDEXDEFRAG. Are they any different.
"Erland Sommarskog" <es****@sommars kog.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****@sommarsk og.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****@sommarsk og.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 constraint is using the
column. Here is the create script for the table
create table mytable
(col1 char(1) not null,
col2 char(1) default 'A' not null
)
|
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 table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.
|
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, then a
re-import of data. Is there any disadvantage to adding this column to
the end of the table. Will this fragment the table. Is a reorg needed?
Thanks,
- CBC
|
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,
Yasaswi
|
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, new size).
This is possible in Oracle (provided you are increasing the column size).
Is there a way to resize the column without having to drop or recreate the
table or column?
Thanks
| | |
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 varchar. I am looking for
something like:
alter table test alter column machineID ... ... varchar
|
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:
ALTER TABLE .
DROP CONSTRAINT
GO
|
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 a column which i by
mistake spelled it wrong was to drop the Whole Table ????
Thanks in Advance ,
Kamal.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
