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 datatype

Hi - I want to change the precision/scale on a decimal datatype on a field
which is in a replicated table (merge). I tried to alter the field on the
publisher but I receive a message saying that I can't do this due to the
table being a replicated table.
Anyone know tips on how to do this (without removing replication). Thanks.
Hi Dave,
unfortunately you can't do this in merge.
Have a look at part (b) in this article for a workaround :
http://www.replicationanswers.com/AddColumn.asp
Alternatively you'll have to reinitialize in the case of merge.
Incidentally, this is no longer an issue in SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
I'll consider re-initialising as I don't have that much data yet and just
conducting tests now anyway. Do I have to pretty much drop replication, do
the changes and start again with merge replication set up?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5dKgDhrFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi Dave,
> unfortunately you can't do this in merge.
> Have a look at part (b) in this article for a workaround :
> http://www.replicationanswers.com/AddColumn.asp
> Alternatively you'll have to reinitialize in the case of merge.
> Incidentally, this is no longer an issue in SQL Server 2005.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Dave - you don't need to drop replication as such - just the merge
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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 the Column datatype

Dear Friends
I need to change the column datatype from Real to Float
as now i am facing the problem when my user put value
140189.14 it is accepting only 104189.1
Please note that i have the same table in merge
replication and i do not want to stop the replication.
Please guide me to solve the problem.
Best regards
Sharad
the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
here are the steps
1) use sp_repladdcolumn to add a dummy column with the float data type. Call
this column dummy
2) update the table on the publisher so the dummy column has the values of
the column whose data type you are trying to change.
3) use sp_repldropcolumn to drop the column containing the real datatype
4) use sp_repladdcolumn to add the column back with the new float data type
5) update the table on the publisher so the new column has the values of the
dummy column
6) use sp_repldropcolumn to drop the dummy column
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
> Dear Friends
> I need to change the column datatype from Real to Float
> as now i am facing the problem when my user put value
> 140189.14 it is accepting only 104189.1
> Please note that i have the same table in merge
> replication and i do not want to stop the replication.
> Please guide me to solve the problem.
> Best regards
> Sharad
|||I have an idea, which may simplify this routine. I did not tried it myself
yet, but it should work just fine I guess
Let's say you have tableX with columnA which type should be changed from
real to float
1) Create temporary tableY with PK column (or columns) from tableX, and also
with columnA
2) Copy data from tableX to tableY
3) use sp_repldropcolumn to drop columnA (containing the real datatype) from
tableX
4) use sp_repladdcolumn to add the columnA back with the new float data type
5) Copy data from tableY column A to tableY columnA
6) Drop temporary tableY
This way replication subsystem has to do following steps:
1) Drop column
2) Add column
3) Process insert of data into columnA
Compared to the steps which should be performed when using earlier mentioned
approach:
1) Add dummy column
2) Process insert of data into dummy column from columnA
3) Drop column
4) Add column
5) Process insert of data into columnA from dummy column
6) Drop dummy column
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
> here are the steps
> 1) use sp_repladdcolumn to add a dummy column with the float data type.
Call
> this column dummy
> 2) update the table on the publisher so the dummy column has the values of
> the column whose data type you are trying to change.
> 3) use sp_repldropcolumn to drop the column containing the real datatype
> 4) use sp_repladdcolumn to add the column back with the new float data
type
> 5) update the table on the publisher so the new column has the values of
the
> dummy column
> 6) use sp_repldropcolumn to drop the dummy column
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Sharad" <niitmalad@.yahoo.co.in> wrote in message
> news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
>
|||Yes, this is a much simpler and better way of doing it. Thanks Kestutis!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uVd2uDA9EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I have an idea, which may simplify this routine. I did not tried it myself
> yet, but it should work just fine I guess
> Let's say you have tableX with columnA which type should be changed from
> real to float
> 1) Create temporary tableY with PK column (or columns) from tableX, and
also
> with columnA
> 2) Copy data from tableX to tableY
> 3) use sp_repldropcolumn to drop columnA (containing the real datatype)
from
> tableX
> 4) use sp_repladdcolumn to add the columnA back with the new float data
type
> 5) Copy data from tableY column A to tableY columnA
> 6) Drop temporary tableY
> This way replication subsystem has to do following steps:
> 1) Drop column
> 2) Add column
> 3) Process insert of data into columnA
> Compared to the steps which should be performed when using earlier
mentioned[vbcol=seagreen]
> approach:
> 1) Add dummy column
> 2) Process insert of data into dummy column from columnA
> 3) Drop column
> 4) Add column
> 5) Process insert of data into columnA from dummy column
> 6) Drop dummy column
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Call
of
> type
> the
>

Change owner user defined datatype

Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner

Thanks in advance
Jef WainHere is the example, this code will change user defined datatype's owner from 'test' to 'dbo' which created by 'test' user

exec sp_configure 'allow updates', '1'

reconfigure WITH OVERRIDE

go

BEGIN TRANSACTION

update systypes set uid = user_id('dbo') where uid = user_id('test')

COMMIT TRANSACTION

exec sp_configure 'allow updates', '0'

reconfigure WITH OVERRIDE

go|||Thank you very much|||sp_changeobjectowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_1lpu.asp) will also do this, in a way that is supported by Microsoft.

-PatP

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
> > I want to change the user defined data type from varchar(5000) to
> > varchar(8000). Do I need to drop all the objects referencing the udt
> > and re-create them?
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?
Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.co m...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may change
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.co m...
Thanks a lot.
|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>

Change of User Defined Datatype

I want to change the user defined data type from varchar(5000) to
varchar(8000). Do I need to drop all the objects referencing the udt
and re-create them?Hi
Yes, Currently UDT's can not be changed, so a drop and re-create is
required. To drop it. it has to be removed from all the objects that depend
on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||Actually, instead of dropping the objects that reference it, you may change
the data type of the columns to the native data type and then change it
back..This is the primary reason I do not use UDTs...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fred" <fredericksiu@.hotmail.com> wrote in message
news:a5795dd.0411152215.17971860@.posting.google.com...
> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#xLY9e9yEHA.1264
@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Actually, instead of dropping the objects that reference it, you may chang
e
> the data type of the columns to the native data type and then change it
> back..This is the primary reason I do not use UDTs...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Fred" <fredericksiu@.hotmail.com> wrote in message
> news:a5795dd.0411152215.17971860@.posting.google.com...
Thanks a lot.|||why not use a tool to do it instead of all that manual labour?
check out DB Ghost at www.dbghost.com
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"Fred" wrote:

> I want to change the user defined data type from varchar(5000) to
> varchar(8000). Do I need to drop all the objects referencing the udt
> and re-create them?
>