Changing column data type increase transaction log

I am using SQL Server 2000. My database is about 8.5 gig in size.
I have a table with a column with datatype smallint, and I would like to
change it to datatype int.
When I did that, it is taking forever and the transaction log just keep
growing and growing until I almost ran out of disk space before I killed
Enterprise Manager.
How can I change a column datatype without making the transaction log keep
growing and growing ?
Thank you.On Mar 20, 12:56=A0pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep=
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||Thank you.
My database model is already simple. If I do the ALTER TABLE will the
transaction log keep growing and growing ?
I also posted another question on this forum regarding moving data from 1
column to another. I am thinking to do it that way.
Here is the real table:
CREATE TABLE [dbo].[Packet] (
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PACKET_DATA] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKET_VOL] [smallint] NULL ,
[PACKET_VOL2] [int] NULL ,
[PACKET_TRADE] [float] NULL
) ON [PRIMARY]
GO
I create PACKET_VOL2 as datatype INT, and I will move data from PACKET_VOL
to PACKET_VOL2 with the following query:
set ROWCOUNT 50000
declare @.LastCount smallint
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
UPDATE PACKET SET PACKET_VOL2 = PACKET_VOL WHERE PACKET_VOL2 is null
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
Then I will rename PACKET_VOL to PACKET_VOLX and PACKET_VOL2 to PACKET_VOL.
What do you think ?
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||If after renaming PACKET_VOL2 to PACKET_VOL, if I want to delete column
PACKET_VOL2 by doing the following:
ALTER TABLE packet DROP COLUMN PACKET_VOLX
Will it eat up the transaction log (will the log keep growing and growing
while I do the ALTER TABLE above) ?
Thank you
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||When I do
ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
it executes very fast, and the transaction log did not grow.
But when I do
ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
the transaction log keeps growing and growing, so I stopped it.
Why when converting to smallint it went fast and the log did not grow but
when converting to INT the log keep growing?
Thanks.
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||> Why when converting to smallint it went fast and the log did not grow but when converting to INT
> the log keep growing?
Some operations are meta-data only operations where for other operations SQL Server need to actually
modify each row. I would guess that there is some information in Books Online about this, but they
can't document every possible change (from type - to type) and whether such change is meta-data only
or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but when converting to INT
> the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log keep
>> growing and growing ?
>> Thank you.
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>|||In addition to Tibor's reply, there is also a third type of change. For
some datatype changes SQL Server has to inspect every row to see if the
existing values 'fit' into the new datatype, and gives you an error if even
one row won't be convertible. But it doesn't actually make any changes. So
there can be lots of reads with no writes.
So ALTER TABLE operations can be one of the following:
1. Metadata only
2. Read the whole table
3. Read and WRITE the whole table
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>
> Some operations are meta-data only operations where for other operations
> SQL Server need to actually modify each row. I would guess that there is
> some information in Books Online about this, but they can't document every
> possible change (from type - to type) and whether such change is meta-data
> only or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>|||If your column is a small int already, and you issue a command to change it
to a small int, since nothing actually needs to change I would expect this
to be a very fast operation.
However, if the data type actually changes, I would expect it to take some
time, depending on the size of the table. I'm not sure how much work SQL
Server needs to do to change the smallint to an int, maybe it needs to
allocate more space in every row?
"fniles" <fniles@.pfmail.com> wrote in message
news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but
> when converting to INT the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>|||> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
Page density will also likely come into play... if the pages are full then,
even though it's only a 2 byte change, there may need to be a large
re-allocation of data...|||Thank you, all.
1. Metadata only -> is this the fast one ?
2. Read the whole table --> is this slow ?
3. Read and WRITE the whole table --> is this the slowest one ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23RhZg80iIHA.5088@.TK2MSFTNGP02.phx.gbl...
> In addition to Tibor's reply, there is also a third type of change. For
> some datatype changes SQL Server has to inspect every row to see if the
> existing values 'fit' into the new datatype, and gives you an error if
> even one row won't be convertible. But it doesn't actually make any
> changes. So there can be lots of reads with no writes.
> So ALTER TABLE operations can be one of the following:
> 1. Metadata only
> 2. Read the whole table
> 3. Read and WRITE the whole table
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>> Why when converting to smallint it went fast and the log did not grow
>> but when converting to INT the log keep growing?
>>
>> Some operations are meta-data only operations where for other operations
>> SQL Server need to actually modify each row. I would guess that there is
>> some information in Books Online about this, but they can't document
>> every possible change (from type - to type) and whether such change is
>> meta-data only or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow
>> but when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like
>> to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I
>> killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>>
>|||Thank you, all.
I was doing some testing.
When I change the column to smallint, it was of type int originally. This
went fast.
Then I changed back from smallint to int, that's when it took a long time.
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:%235NGxW1iIHA.5504@.TK2MSFTNGP05.phx.gbl...
> If your column is a small int already, and you issue a command to change
> it to a small int, since nothing actually needs to change I would expect
> this to be a very fast operation.
> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>|||fniles (fniles@.pfmail.com) writes:
> 1. Metadata only -> is this the fast one ?
Yes, this is instant.
> 2. Read the whole table --> is this slow ?
Takes some more time yes. But your log will not be affected.
> 3. Read and WRITE the whole table --> is this the slowest one ?
Yes, and your transaction log takes a toll if the table is big.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx