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

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:56Xpm, "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.
|||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...
>
> 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...
>
|||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:
> 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...
>
|||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...
>

Changing a Column datatype

Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank youJust change it using Enterprise Manager. The existing values will be implicitly converted.|||i must change it from a script
but i found it

thank you|||Change it in Enterprise Manager, and then click the icon that scripts your change.

Change TinyInt identity into SmallInt

Hi,
I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
change the column to a SmallInt.
I tried this:
drop index behaviorprop.PK_BehaviorProp
alter table behaviorprop alter column BP_ID smallint
but I get
An explicit DROP INDEX is not allowed on index
'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
constraint enforcement.
How can I remove the primary key constraint and add it again after the
change?
Thanks in advance,
Stijn Verrept.You need to drop the constraint instead...
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Hi
create table test (c1 tinyint identity(1,1) not null)
insert into test default values
alter table test alter column c1 int
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Tony Rogerson wrote:

> You need to drop the constraint instead...
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key
> ( bp_id )
> commit tran
Thanks for your reply, but then I get:
'PK_Behaviourprop' is not a constraint.
and when I look in enterprise manager at constraints there aren't any
there, only the primary key (in the indexes tab).
Kind regards,
Stijn Verrept.|||Oh, I did not care about PRIMARY KEY constraint , see Tony's answer
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ucjy$rMeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Hi
> create table test (c1 tinyint identity(1,1) not null)
> insert into test default values
> alter table test alter column c1 int
>
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
>|||Need your version, use PRINT @.@.VERSION, the CREATE TABLE script including
indexes and any constraints.
And, the exact error message (cut and paste).
many thanks
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:SI-dnbH15pMVBvTZRVnyvQ@.scarlet.biz...
> Tony Rogerson wrote:
>
> Thanks for your reply, but then I get:
> 'PK_Behaviourprop' is not a constraint.
> and when I look in enterprise manager at constraints there aren't any
> there, only the primary key (in the indexes tab).
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Need your version, use PRINT @.@.VERSION, the CREATE TABLE script
> including indexes and any constraints.
> And, the exact error message (cut and paste).
> many thanks
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Error message:
Server: Msg 3728, Level 16, State 1, Line 2
'PK_Behaviourprop' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors.
The create table script is at: http://www.entrysoft.com/script.sql it
will create temptest database and will display the error when you try
to run
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
It must be something simple.
Thanks for taking the time!
Kind regards,
Stijn Verrept.|||Not really answering the question but this script will work.... Good (new)
sql 2005 management studio and the create change script option on modify
table ;)
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Users
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Seniors
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BehaviorProp
(
BP_ID smallint NOT NULL IDENTITY (1, 1),
BP_SNID int NOT NULL,
BP_Stamp smalldatetime NOT NULL,
BP_Score1 tinyint NOT NULL,
BP_Score2 tinyint NOT NULL,
BP_Score3 tinyint NOT NULL,
BP_Score4 tinyint NOT NULL,
BP_Score5 tinyint NOT NULL,
BP_Score6 tinyint NOT NULL,
BP_Desc1 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc2 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc3 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc4 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc5 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc6 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_USID smallint NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp ON
GO
IF EXISTS(SELECT * FROM dbo.BehaviorProp)
EXEC('INSERT INTO dbo.Tmp_BehaviorProp (BP_ID, BP_SNID, BP_Stamp,
BP_Score1, BP_Score2, BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1,
BP_Desc2, BP_Desc3, BP_Desc4, BP_Desc5, BP_Desc6, BP_USID)
SELECT CONVERT(smallint, BP_ID), BP_SNID, BP_Stamp, BP_Score1, BP_Score2,
BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1, BP_Desc2, BP_Desc3,
BP_Desc4, BP_Desc5, BP_Desc6, BP_USID FROM dbo.BehaviorProp WITH (HOLDLOCK
TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp OFF
GO
DROP TABLE dbo.BehaviorProp
GO
EXECUTE sp_rename N'dbo.Tmp_BehaviorProp', N'BehaviorProp', 'OBJECT'
GO
ALTER TABLE dbo.BehaviorProp ADD CONSTRAINT
PK_BehaviorProp PRIMARY KEY CLUSTERED
(
BP_ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Seniors FOREIGN KEY
(
BP_SNID
) REFERENCES dbo.Seniors
(
SN_ID
) ON DELETE CASCADE
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Users FOREIGN KEY
(
BP_USID
) REFERENCES dbo.Users
(
US_ID
) ON DELETE CASCADE
GO
COMMIT
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:Ke-dnSqDzbrrO_TZRVny2w@.scarlet.biz...
> Tony Rogerson wrote:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> Error message:
> Server: Msg 3728, Level 16, State 1, Line 2
> 'PK_Behaviourprop' is not a constraint.
> Server: Msg 3727, Level 16, State 1, Line 2
> Could not drop constraint. See previous errors.
> The create table script is at: http://www.entrysoft.com/script.sql it
> will create temptest database and will display the error when you try
> to run
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key (
> bp_id )
> commit tran
>
> It must be something simple.
> Thanks for taking the time!
>
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Not really answering the question but this script will work.... Good
> (new) sql 2005 management studio and the create change script option
> on modify table ;)
This works indeed! Thanks!
Kind regards,
Stijn Verrept.