Reclaiming space 1

[Ant0]

Hi there

Could you tell me the best way to reclaim unused space in tables. Currently our process is to BCP the table out, drop the table, create the table, BCP the data in and create the indexes?

Does DBCC save this procedure. I have tried searching for an artical on this, but am struggling to find anything concrete.

Thanks

 

[SQLBill]

Unused space in Tables or in the database? You can shrink your database files using DBCC SHRINKDATABASE or DBCC SHRINKFILE commands. Refer to the BOL for more information or search this forum and FORUM183 for those commands.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

There isn't much of a way to reclaim unused space in the tables in SQL Server. Microsoft doesn't really let us manage the space on the table level like Oracle does. (It's something that I'd like to see included.)

By rebuilding the clustered index all your data will be resorted within the pages so long as you only have one file per file group. When using any of the DBCC commands data is not moved between files within the file group.

How much space are you loosing within your tables? How are you comming to this conclusion?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Ant0]

funny you ask how I am coming to the conclusion.

I am trying to prove to another guy that his suggestion, currently in place, to bcp out, drop, create table, and bcp in the data will reclaim space (that he can't "prove" needs to be reclaimed) at table level.

The reason I ask is I know you can "alter table ..." in Oracle to reclaim space and want to suggest the same type of SQL for MS SQL.

Thanks for your thoughts.

 

[mrdenny]

SQL Manages that by it self.

In Oracle you have to assign space to each table. You don't have to do that in SQL Server. In SQL I know of 0 benifit to doing this becuase there isn't a need to do this in SQL because space isn't assigned to a specific table.

When you delete data from a table the freed space is returned to the database. When you add data to the table space is taken from the database and given to the table. We don't manage at this level in SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)