Changing column size in existing databese?

I must increase column (filed) size in existing datebase but without using
Enterprise manager...(Becouse we use MSDE on our clients PCs)
The Filed is part of primary and foreign key constraints...
And every constraint has diferent index number in each database...
for example (PK_something_9e382hjl8), and I don't know how to pick this
value before "drop constraint" command....

Thank you very much...[posted and mailed, please reply in news]

Poted (dario1975@.post.hnet.hr) writes:
> I must increase column (filed) size in existing datebase but without
> using Enterprise manager...(Becouse we use MSDE on our clients PCs) The
> Filed is part of primary and foreign key constraints... And every
> constraint has diferent index number in each database... for example
> (PK_something_9e382hjl8), and I don't know how to pick this value before
> "drop constraint" command....

Moral: name your constraint explicitly according to some standardized
scheme, so you easily can find the names.

This query gives you the name of the PK for a table:

select name from sysobjects
where xtype = 'PK'
and parent_obj = object_id('tablename')

This query lists all referencing foreign key for a table:

select object_name(constid), object_name(fkeyid), col_name(fkeyid, fkey)
from sysforeignkeys
where rkeyid = object_id('depots')
order by constid, fkey

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

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

Changing column length of a replicated table

Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

Are you using SQL Server 2005? If so, you can turn on @.replicate_ddl option (set it to 1) for a publication then use regular "ALTER TABLE ... ALTER COLUMN ..." DDL syntax to increase the length of the column, the DDL change will be automatically replicated to subscriber by replication. (@.replication_ddl options is automatically turned on if you use management studio to create the publication)

Hope that helps,

Zhiqiang Feng

|||

Thank you Zhiqiang for your reply, but sorry we're using 2000.

Opal

|||

Sorry there is no easy workaround in SQL Server 2000. The only alternative is that you can use sp_repldropcolumn then sp_repladdcolumn to drop and re-create the column, but that way you will lose all the data for that column.

Thanks,

Zhiqiang Feng

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...
>

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?
The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>
|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>
|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>

Change sql server connections from 32767 to 60000 or more

Is that possible ?
BOL states that max user connections is 32767. Is that hard coded with SQL ?
Or can I increase that to 60000 and have SQL open 60000 connections.
Yes you may ask, why do I need and i understand that it all depends on
capacity.
But my question is fairly simple and assuming load,etc are all taken account
of, i want to open say 60,000 connections and if i specify 60,000 as max
user connections using sp_configure, will it work ?The largest SQL Server systems in the world do not use that many connections
and I am sure you won't either. That does not mean you can't have more than
32K users connected. The key is to use connection pooling. With proper use
of connection pooling you can service many more users than you have actual
connections since rarely are they all actually busy at the same time.
--
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Is that possible ?
> BOL states that max user connections is 32767. Is that hard coded with SQL
> ? Or can I increase that to 60000 and have SQL open 60000 connections.
> Yes you may ask, why do I need and i understand that it all depends on
> capacity.
> But my question is fairly simple and assuming load,etc are all taken
> account of, i want to open say 60,000 connections and if i specify 60,000
> as max user connections using sp_configure, will it work ?
>|||I understand all of that Andrew and was only curious to know if I can have
more than 32767 connections and if so, can i increase it using sp_configure
?
Also, where can i read more about connection pooling
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> The largest SQL Server systems in the world do not use that many
> connections and I am sure you won't either. That does not mean you can't
> have more than 32K users connected. The key is to use connection pooling.
> With proper use of connection pooling you can service many more users than
> you have actual connections since rarely are they all actually busy at the
> same time.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>> Is that possible ?
>> BOL states that max user connections is 32767. Is that hard coded with
>> SQL ? Or can I increase that to 60000 and have SQL open 60000
>> connections.
>> Yes you may ask, why do I need and i understand that it all depends on
>> capacity.
>> But my question is fairly simple and assuming load,etc are all taken
>> account of, i want to open say 60,000 connections and if i specify 60,000
>> as max user connections using sp_configure, will it work ?
>>
>|||In general when the specifications in BooksOnLine states that something is
the Maximum you can be pretty sure you can not exceed that<g>. As for
Connection Pooling I would do a Google search and be sure to specify what
type of drivers you are using to connect.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:eLDIS0A7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>I understand all of that Andrew and was only curious to know if I can have
>more than 32767 connections and if so, can i increase it using sp_configure
>?
> Also, where can i read more about connection pooling
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwFzKf$6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>> The largest SQL Server systems in the world do not use that many
>> connections and I am sure you won't either. That does not mean you can't
>> have more than 32K users connected. The key is to use connection
>> pooling. With proper use of connection pooling you can service many more
>> users than you have actual connections since rarely are they all actually
>> busy at the same time.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <hassanboy@.hotmail.com> wrote in message
>> news:eBpYCy96FHA.4036@.TK2MSFTNGP11.phx.gbl...
>> Is that possible ?
>> BOL states that max user connections is 32767. Is that hard coded with
>> SQL ? Or can I increase that to 60000 and have SQL open 60000
>> connections.
>> Yes you may ask, why do I need and i understand that it all depends on
>> capacity.
>> But my question is fairly simple and assuming load,etc are all taken
>> account of, i want to open say 60,000 connections and if i specify
>> 60,000 as max user connections using sp_configure, will it work ?
>>
>>
>