|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Alter column on big table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Alter column on big table
|
|
| sandiyan 2002-11-18, 11:23 am |
| I have a requirement to alter a column from:
varbinary(255) to varbinary(7000).
This table contains about 50 million rows in it. Hence, this message for advice...
I am planning to run below script to migrate this table:
ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
GO
Is there anyhting I have to watch for... e.g.:
1. Does Alter column use TEMPDB or transaction log at all?
If so,
2. Do I have to do the alter in a batched update - whithin explicit transaction.
Thanks,
Sandiyan
| |
| Russell Fields 2002-11-18, 1:23 pm |
| Sandiyan,
First, have you run this on a development server before trying on
production. (Or is this the development server?)
Obviously, an ALTER statement is a transaction, so altering a table could
have a large impact on the transaction log. Consider adding a char(50)
column with a default - every row in the table would have to be updated.
Since this particular alteration is simply increasing the maximum allowable
size for a column, but not changing its type, I believe that the transaction
will be short. (But test first on a smaller scale.)
Russell Fields
"sandiyan" <sandiyan@yahoo.co.uk> wrote in message
news:69e9c64b.0211180813.60b3ede3@posting.google.com...
> I have a requirement to alter a column from:
> varbinary(255) to varbinary(7000).
> This table contains about 50 million rows in it. Hence, this message for
advice...
>
> I am planning to run below script to migrate this table:
>
> ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
> GO
>
> Is there anyhting I have to watch for... e.g.:
> 1. Does Alter column use TEMPDB or transaction log at all?
> If so,
> 2. Do I have to do the alter in a batched update - whithin explicit
transaction.
>
> Thanks,
> Sandiyan
| |
| Hal Berenson 2002-11-18, 7:23 pm |
| Increasing a variable length column's maximum length should be short, sweet,
and painless as it need not modify any data rows. It simply updates
metadata in the system tables. Now if you alter the length of a fixed size
column, look out!
--
Hal Berenson
True Mountain Consulting
"sandiyan" <sandiyan@yahoo.co.uk> wrote in message
news:69e9c64b.0211180813.60b3ede3@posting.google.com...
> I have a requirement to alter a column from:
> varbinary(255) to varbinary(7000).
> This table contains about 50 million rows in it. Hence, this message for
advice...
>
> I am planning to run below script to migrate this table:
>
> ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
> GO
>
> Is there anyhting I have to watch for... e.g.:
> 1. Does Alter column use TEMPDB or transaction log at all?
> If so,
> 2. Do I have to do the alter in a batched update - whithin explicit
transaction.
>
> Thanks,
> Sandiyan
| |
| sandiyan 2002-11-19, 7:23 am |
| Thanks for that. I tested against 10 million row table and was fairly
instantaneous.
I second requirement is to increase a fixed column from char(3) to
char(4).
Again, the table has about 50 million rows(1.6GB in size).
I tried to run the following and ran out of transaction log
space(1GB).
ALTER TABLE [dbo].[Mytable] Alter column [Member] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
GO
How can I get around this transaction log problem...? - apart from
increasing log space...
Thanks,
Sandiyan
"Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message news:<#fU4PP2jCHA.2580@tkmsftngp12>...
> Increasing a variable length column's maximum length should be short, sweet,
> and painless as it need not modify any data rows. It simply updates
> metadata in the system tables. Now if you alter the length of a fixed size
> column, look out!
>
> --
> Hal Berenson
> True Mountain Consulting
>
>
> "sandiyan" <sandiyan@yahoo.co.uk> wrote in message
> news:69e9c64b.0211180813.60b3ede3@posting.google.com...
> > I have a requirement to alter a column from:
> > varbinary(255) to varbinary(7000).
> > This table contains about 50 million rows in it. Hence, this message for
> advice...
> >
> > I am planning to run below script to migrate this table:
> >
> > ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
> > GO
> >
> > Is there anyhting I have to watch for... e.g.:
> > 1. Does Alter column use TEMPDB or transaction log at all?
> > If so,
> > 2. Do I have to do the alter in a batched update - whithin explicit
> transaction.
> >
> > Thanks,
> > Sandiyan
| |
| Tibor Karaszi 2002-11-19, 8:23 am |
| SQL Server will need as much space in the log as it needs. I don't think there's any real way
around it. (Except create a new table, copy over data etc). You might get away with some less
log space if you drop all indexes first, truncate log, alter table, truncate log, add indexes...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"sandiyan" <sandiyan@yahoo.co.uk> wrote in message
news:69e9c64b.0211190510.ce314c5@posting.google.com...
> Thanks for that. I tested against 10 million row table and was fairly
> instantaneous.
> I second requirement is to increase a fixed column from char(3) to
> char(4).
> Again, the table has about 50 million rows(1.6GB in size).
> I tried to run the following and ran out of transaction log
> space(1GB).
>
> ALTER TABLE [dbo].[Mytable] Alter column [Member] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> GO
>
> How can I get around this transaction log problem...? - apart from
> increasing log space...
>
> Thanks,
> Sandiyan
>
> "Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message
news:<#fU4PP2jCHA.2580@tkmsftngp12>...
> > Increasing a variable length column's maximum length should be short, sweet,
> > and painless as it need not modify any data rows. It simply updates
> > metadata in the system tables. Now if you alter the length of a fixed size
> > column, look out!
> >
> > --
> > Hal Berenson
> > True Mountain Consulting
> >
> >
> > "sandiyan" <sandiyan@yahoo.co.uk> wrote in message
> > news:69e9c64b.0211180813.60b3ede3@posting.google.com...
> > > I have a requirement to alter a column from:
> > > varbinary(255) to varbinary(7000).
> > > This table contains about 50 million rows in it. Hence, this message for
> > advice...
> > >
> > > I am planning to run below script to migrate this table:
> > >
> > > ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
> > > GO
> > >
> > > Is there anyhting I have to watch for... e.g.:
> > > 1. Does Alter column use TEMPDB or transaction log at all?
> > > If so,
> > > 2. Do I have to do the alter in a batched update - whithin explicit
> > transaction.
> > >
> > > Thanks,
> > > Sandiyan
| |
| sandiyan 2002-11-21, 7:23 am |
| Thanks.
This is what I have created to deal with big tables to alter fixed
size columns.
1. Set recovery to BULK_LOGGED
2. Do a select into - convert on the fly...(7mins)
select cl1, convert(char(4), cl2) as cl2 into temp_tbl from master_tbl
3. drop original table
4. re-name table
5. Add constraints
6. Add index (12 mins)
7. Change recover back onto FULL
All in all, whole process to about 20 mins(for 10 million rows - 1.6GB
in size) to complete.
Fingers crossed when we do production machines.
Above method is lot faster hassle free(no need to increase space for
tran log)
Thanks to all.
Sandiyan.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> wrote in message news:<ufCowW9jCHA.1328@tkmsftngp09>...
> SQL Server will need as much space in the log as it needs. I don't think there's any real way
> around it. (Except create a new table, copy over data etc). You might get away with some less
> log space if you drop all indexes first, truncate log, alter table, truncate log, add indexes...
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "sandiyan" <sandiyan@yahoo.co.uk> wrote in message
> news:69e9c64b.0211190510.ce314c5@posting.google.com...
> > Thanks for that. I tested against 10 million row table and was fairly
> > instantaneous.
> > I second requirement is to increase a fixed column from char(3) to
> > char(4).
> > Again, the table has about 50 million rows(1.6GB in size).
> > I tried to run the following and ran out of transaction log
> > space(1GB).
> >
> > ALTER TABLE [dbo].[Mytable] Alter column [Member] [char] (4) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL
> > GO
> >
> > How can I get around this transaction log problem...? - apart from
> > increasing log space...
> >
> > Thanks,
> > Sandiyan
> >
> > "Hal Berenson" < haroldb@truemountainconsulting
.com> wrote in message
> news:<#fU4PP2jCHA.2580@tkmsftngp12>...
> > > Increasing a variable length column's maximum length should be short, sweet,
> > > and painless as it need not modify any data rows. It simply updates
> > > metadata in the system tables. Now if you alter the length of a fixed size
> > > column, look out!
> > >
> > > --
> > > Hal Berenson
> > > True Mountain Consulting
> > >
> > >
> > > "sandiyan" <sandiyan@yahoo.co.uk> wrote in message
> > > news:69e9c64b.0211180813.60b3ede3@posting.google.com...
> > > > I have a requirement to alter a column from:
> > > > varbinary(255) to varbinary(7000).
> > > > This table contains about 50 million rows in it. Hence, this message for
> advice...
> > > >
> > > > I am planning to run below script to migrate this table:
> > > >
> > > > ALTER TABLE [dbo].[Mytable] Alter column [msgBody] [varbinary] (7000) NULL
> > > > GO
> > > >
> > > > Is there anyhting I have to watch for... e.g.:
> > > > 1. Does Alter column use TEMPDB or transaction log at all?
> > > > If so,
> > > > 2. Do I have to do the alter in a batched update - whithin explicit
> transaction.
> > > >
> > > > Thanks,
> > > > Sandiyan
|
|
|
|
|