|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Adding a column on a large 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 |
Adding a column on a large table
|
|
| saisat 2002-11-19, 5:23 pm |
| Hello all,
I am adding a timestamp column to a table with 40 million rows(50Gigs).
Transaction log gets full and displays an error message. I tried truncate
log on checkpoint. But that doesnt work. I am not sure why.
Is there a way to solve this problem without increasing the disk size.
Thanks
Satish
| |
| Uri Dimant 2002-11-20, 3:23 am |
| saisat
I think you did it by EM.
You need to doit by t-sql alter table -add column(see bol)
saisat <satish_sainath@hotmail.com> wrote in message
news:uDKnotBkCHA.2216@tkmsftngp12...
> Hello all,
>
> I am adding a timestamp column to a table with 40 million rows(50Gigs).
> Transaction log gets full and displays an error message. I tried truncate
> log on checkpoint. But that doesnt work. I am not sure why.
> Is there a way to solve this problem without increasing the disk size.
>
> Thanks
> Satish
>
>
| |
| saisat 2002-11-20, 9:23 am |
| Hi Uri,
I did use the
alter table tablename add ts timestamp not null
but the problem is, since there are 40 million rows and this is a update,
the transaction log keeps a copy of both the original version of the row and
the new version of the row. So the disk size increases dramatically. The
obvious solution is to increase the diskspace. But currently we dont have
the immediate resources for that. So I would like to know how to prevent the
transaction log from growing when i run the above query.
I tried tuncate log on chkpoint but I dont think the check point operation
takes place until this query is completed.
-satish
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:ubcbS7GkCHA.440@tkmsftngp11...
> saisat
> I think you did it by EM.
> You need to doit by t-sql alter table -add column(see bol)
>
> saisat <satish_sainath@hotmail.com> wrote in message
> news:uDKnotBkCHA.2216@tkmsftngp12...
> > Hello all,
> >
> > I am adding a timestamp column to a table with 40 million rows(50Gigs).
> > Transaction log gets full and displays an error message. I tried
truncate
> > log on checkpoint. But that doesnt work. I am not sure why.
> > Is there a way to solve this problem without increasing the disk size.
> >
> > Thanks
> > Satish
> >
> >
>
>
| |
| John Fichera 2002-11-20, 1:23 pm |
| I suppose you could minimize the logging by performing a select into and
casting the new column on the fly. Kinda like this:
select col1,col2,cast('' as timestamp) as tststamp into MyTest from
TheReallyBigTable
| |
| Charles Sands 2002-11-20, 4:23 pm |
| "saisat" <satish_sainath@hotmail.com> wrote in message news:<OE8B8YKkCHA.2672@tkmsftngp09>...
> Hi Uri,
> I did use the
>
> alter table tablename add ts timestamp not null
>
> but the problem is, since there are 40 million rows and this is a update,
> the transaction log keeps a copy of both the original version of the row and
> the new version of the row. So the disk size increases dramatically. The
> obvious solution is to increase the diskspace. But currently we dont have
> the immediate resources for that. So I would like to know how to prevent the
> transaction log from growing when i run the above query.
> I tried tuncate log on chkpoint but I dont think the check point operation
> takes place until this query is completed.
>
> -satish
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:ubcbS7GkCHA.440@tkmsftngp11...
> > saisat
> > I think you did it by EM.
> > You need to doit by t-sql alter table -add column(see bol)
> >
> > saisat <satish_sainath@hotmail.com> wrote in message
> > news:uDKnotBkCHA.2216@tkmsftngp12...
> > > Hello all,
> > >
> > > I am adding a timestamp column to a table with 40 million rows(50Gigs).
> > > Transaction log gets full and displays an error message. I tried
> truncate
> > > log on checkpoint. But that doesnt work. I am not sure why.
> > > Is there a way to solve this problem without increasing the disk size.
> > >
> > > Thanks
> > > Satish
> > >
> > >
> >
> >
Try this, set your database to simple recovery model. Script the
existing table and then add your column to the script. Change the
table name in the script and run to create a new table that is a
duplicate of the existing plus the new column. Then you can do a
straight insert into the new table from the old table. Once that is
done you can drop or rename the original table and then rename the new
table to the old table name. Make sure that you scripted out the
indexes so that the new table has all the same indexes as the old one.
hth,
Charles
|
|
|
|
|