question about log file size when alter Huge table

[Gordowey]

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.

Is this the only way to alter a single field in my table??

I would like to heard you opinions...Than ks..

ALberto

[Greg D. Moore \(Strider\)]

"Gordowey" <al************ @gmail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.

Sounds about right.

Is this the only way to alter a single field in my table??
Well, you might be able to ADD a new column to your table, that might work
better. (especially if it has no default).

Or, it might be easier to BCP the data out, truncate the table, BCP the data
in.

(in this case I don't think native format would work so you'd have to
experiment.)


I would like to heard you opinions...Than ks..

ALberto

[Erland Sommarskog]

Gordowey (al************ @gmail.com) writes:

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.
Yup, that's it.
Is this the only way to alter a single field in my table??

Rather than having ALTER TABLE to all that under the covers, you could do
it your self. The twist is that then you can do the insert in batches,
and truncate the transaction log between the turns. (Simplest is to run
in simple recovery mode if you can.) When you compose the batches, use
the clustered index for the table, else selection of the batches may be
horribly slow.

--
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