Alter table problems (transaction log will growth to 60 Gb...)

I have a large table, and I try alter one column:
ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
The transaction log will growth to 60 Gb, and then I haven't any more disk
space avalable.
Does the SQL server have any parameter like NOLOGGING
or does somebody have other solutions how I can solve this problem?You could change the database recovery model to simple, make the change, and
then revert the recovery model.
Alternatively, you could create a new table with the column change, and them
move the data to the new table -perhaps in batches, truncating the log in
between.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:ODG1jGx0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a large table, and I try alter one column:
> ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME DECIMAL (15,9)
> The transaction log will growth to 60 Gb, and then I haven't any more disk
> space avalable.
> Does the SQL server have any parameter like NOLOGGING
> or does somebody have other solutions how I can solve this problem?
>
>|||> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
OK, thanks. I allready have simple recovery mode in use, so I should try
via new table...|||Also, consider creating the new table using SELECT INTO, which is
minimally-logged in the SIMPLE model.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
>> You could change the database recovery model to simple, make the change,
>> and then revert the recovery model.
>> Alternatively, you could create a new table with the column change, and
>> them move the data to the new table -perhaps in batches, truncating the
>> log in between.
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Hello,
Even Simple recovery mode will log the activities and will clear the log
once the task is completed. What you can do is
script entire the table including dependents then:-.
1. Using the script create the Table with new name. Do not create index and
triggers
2. Use DTS to load the data into new table. or use BCP OUT and BCP/IN or
BULK insert with Batch commit option to load data
3. Once the load is completed
4. Verify the count of records in both table
5. Truncate the old table
6. Rename the new table to existing using sp_rename
7. Create the indexes and triggers (Step 7 can be done along with step 1
also, but the load will be slightly slow)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:e$O0zRx0GHA.4976@.TK2MSFTNGP02.phx.gbl...
>> You could change the database recovery model to simple, make the change,
>> and then revert the recovery model.
>> Alternatively, you could create a new table with the column change, and
>> them move the data to the new table -perhaps in batches, truncating the
>> log in between.
> OK, thanks. I allready have simple recovery mode in use, so I should try
> via new table...
>|||Arnie Rowland wrote:
> You could change the database recovery model to simple, make the change, and
> then revert the recovery model.
> Alternatively, you could create a new table with the column change, and them
> move the data to the new table -perhaps in batches, truncating the log in
> between.
>
Simple mode alone won't resolve this, the ALTER will still take place as
a single, giant transaction. See
http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
(laugh, Arnie).
Batching is the way to avoid this...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O7ngVKx0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> You could change the database recovery model to simple, make the change,
> and then revert the recovery model.
I'm not sure that would do it. I'm under the impression that it's still
a logged operation -- still need to be able to rollback if it fails -- but
the log space is freed immediately after the statement completes.
I don't think that will work.
> Alternatively, you could create a new table with the column change, and
> them move the data to the new table -perhaps in batches, truncating the
> log in between.
That's what I'd recommend.