Primary file & tan Log grow crazy!

HI:
I have a Table about 3GB in size. Whenever I try to add a column or set
a primary key to this table from the Enterprise Manager, the operation
would take a long time and the MDF and LOG file will just keep growing
crazy (more then 3GB) that it finanlly eats up all my hard disk space
and I have to kill the process.
So, how many HD space does it need to modify a 3GB Table (eg. add a
column). Actually what is the ratio? Is there any better way to modify
a large Table?
I have also enable the Auto-Shrink properties of the Table but still no
use.
Much appreciate for any advise.JJ wrote:
> HI:
> I have a Table about 3GB in size. Whenever I try to add a column or
> set a primary key to this table from the Enterprise Manager, the
> operation would take a long time and the MDF and LOG file will just
> keep growing crazy (more then 3GB) that it finanlly eats up all my
> hard disk space and I have to kill the process.
> So, how many HD space does it need to modify a 3GB Table (eg. add a
> column). Actually what is the ratio? Is there any better way to modify
> a large Table?
> I have also enable the Auto-Shrink properties of the Table but still
> no use.
> Much appreciate for any advise.
You should always make these types of changes using good old ALTER TABLE
commands from your query tool and avoid using SQL EM for these tasks.
While SQL EM seems like an easy alternative, depending on the operation,
SQL EM may actually be creating a copy of the table with the changes and
manually migrating the data. To see what SQL EM is doing you can either
watch the activity in Profiler or script out the changes in SQL EM.
Setting a PK in a table should not cause a recreate of the table, but if
you create the PK as clustered, then SQL Server has to reorg the entire
3GB table, which could be quite time consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use change scripts for that, EMs doing something a useless stuff around
the scripts. If you make your changes in a DDL scripts you should be
able to execute that much easier, even because the addition of a
primary key has no need to recreate the table as it is in the case of
an identity column.
HTH, Jens Suessmeyer.