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.

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...
> 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...
> 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, a
nd
> then revert the recovery model.
> Alternatively, you could create a new table with the column change, and th
em
> 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
saction-Log.html" target="_blank">http://realsqlguy.com/serendipity/a...action-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.

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now for some reason instead of using the index for it is using a sequential scan. I have updated the statistics, rebuilt the indexes and about everthing else I can think of. It simply refuses to use the index it did prior to the alter
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
--
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||The columns that were altered are NOT part of the index nor are they used in the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
--
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...
> The columns that were altered are NOT part of the index nor are they used
in the criteria of the query.
>

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now f
or some reason instead of using the index for it is using a sequential scan.
I have updated the statistics, rebuilt the indexes and about everthing els
e I can think of. It simply
refuses to use the index it did prior to the alter.
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||The columns that were altered are NOT part of the index nor are they used in
the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...

quote:

---

> The columns that were altered are NOT part of the index nor are they used

---

in the criteria of the query.

quote:

---

>

---

Alter table alter column in MSACCESS. How can I do it for a decimal field?

Hi people,

I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.

Example:
table : item_nota_fiscal_forn_setor_publico
field : qtd_mercadoria integer NOT NULL

ALTER TABLE item_nota_fiscal_forn_setor_publico
ALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULL

But, It doesn't work. A sintax error rises.

I need to change that field in a Visual Basic aplication, dinamically.

How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?

Thanks,

Euler Almeida

--
Message posted via http://www.sqlmonster.comEuler Almeida via SQLMonster.com (forum@.SQLMonster.com) writes:
> I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS
> 2K.
> Example:
> table : item_nota_fiscal_forn_setor_publico
> field : qtd_mercadoria integer NOT NULL
> ALTER TABLE item_nota_fiscal_forn_setor_publico
> ALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULL
> But, It doesn't work. A sintax error rises.

I didn't not get any syntax error. Then again, I tried this on SQL Server,
since SQL Server is the focus for this newsgroup.

If you are working with an Access database, you are better of in an
Access newsgroup like comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers

Allowable Decimal Value

Can anyone tell me what is the biggest value that can go
into Decimal (9) field in SQL 7.0 table '. I got an error
when inserting '99999999999'.
Thanks for help.Surely a decimal(9) can store up to 9 digits?
e.g. 999999999
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||A decimal column can hold a value with the maximum number of digits that is
defined as it's precision (that's the number between brackets. So decimal(9)
will only hold values with a maximum of 9 digits and not 11 like you try to
insert.
--
Jacco Schalkwijk
SQL Server MVP
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:d48101c3eff2$5cbead30$a101280a@.phx.gbl...
> Can anyone tell me what is the biggest value that can go
> into Decimal (9) field in SQL 7.0 table '. I got an error
> when inserting '99999999999'.
> Thanks for help.|||But it does insert this: 00232575945
>--Original Message--
>Surely a decimal(9) can store up to 9 digits?
>e.g. 999999999
>
>--
>Cheers,
>James Goodman MCSE, MCDBA
>http://www.angelfire.com/sports/f1pictures
>
>.
>|||Sorry. My bet, It ignores the '0' s.
Thanks.
>--Original Message--
>But it does insert this: 00232575945
>
>
>>--Original Message--
>>Surely a decimal(9) can store up to 9 digits?
>>e.g. 999999999
>>
>>--
>>Cheers,
>>James Goodman MCSE, MCDBA
>>http://www.angelfire.com/sports/f1pictures
>>
>>.
>.
>

Allowable Decimal Value

Can anyone tell me what is the biggest value that can go
into Decimal (9) field in SQL 7.0 table '. I got an error
when inserting '99999999999'.
Thanks for help.Surely a decimal(9) can store up to 9 digits?
e.g. 999999999
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||A decimal column can hold a value with the maximum number of digits that is
defined as it's precision (that's the number between brackets. So decimal(9)
will only hold values with a maximum of 9 digits and not 11 like you try to
insert.
Jacco Schalkwijk
SQL Server MVP
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:d48101c3eff2$5cbead30$a101280a@.phx.gbl...
> Can anyone tell me what is the biggest value that can go
> into Decimal (9) field in SQL 7.0 table '. I got an error
> when inserting '99999999999'.
> Thanks for help.|||But it does insert this: 00232575945

>--Original Message--
>Surely a decimal(9) can store up to 9 digits?
>e.g. 999999999
>
>--
>Cheers,
>James Goodman MCSE, MCDBA
>http://www.angelfire.com/sports/f1pictures
>
>.
>|||Sorry. My bet, It ignores the '0' s.
Thanks.

>--Original Message--
>But it does insert this: 00232575945
>
>
>.
>