adding a new column

Dear all,

What would be the fastest way to add a new column (a timestamp column
to be exact) to a table with about 15M records?

We actually tried doing that earlier, but it was already running for 6
hours and the script has not yet finished executing.  We had to kill
the transaction as it was already severely prolonging the downtime.
The rollback process, of course, incurred almost 4.5 hours in itself.
:(

Since we will be doing this again (as we need the timestamp table),
any tips would be appreciated.

Thanks.

Aramid

How did you do it the first time?  I assume you used Enterprise Manager
which is a real no-no when it comes to modifying tables.  The alter table
command from Query analyzer would have been much better.  It's hard to say
how long it should take without knowing a lot more of your hardware and
configurations.  Another option is to BCP out all the data from that table.
Truncate it.  Set the recovery mode to Simple. Alter the table to add the
column.  Then BCP it back in. You may need a format file to BCP back in
since the structure will change.  In any case make sure you do a full backup
first and set the recovery mode back to what it was if changed.

--
Andrew J. Kelly  SQL MVP


"Aramid" <aramid@hotmail.com> wrote in message
 news:vod5m11210l0l0v
 8568nik7nvlfsjf9gno@
4ax.com...
> Dear all,
>
> What would be the fastest way to add a new column (a timestamp column
> to be exact) to a table with about 15M records?
>
> We actually tried doing that earlier, but it was already running for 6
> hours and the script has not yet finished executing.  We had to kill
> the transaction as it was already severely prolonging the downtime.
> The rollback process, of course, incurred almost 4.5 hours in itself.
> :(
>
> Since we will be doing this again (as we need the timestamp table),
> any tips would be appreciated.
>
> Thanks.
>
> Aramid

On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
< sqlmvpnooospam@shadh
awk.com> wrote:

>How did you do it the first time?  I assume you used Enterprise Manager
>which is a real no-no when it comes to modifying tables.  The alter table
>command from Query analyzer would have been much better.  It's hard to say
>how long it should take without knowing a lot more of your hardware and
>configurations.  Another option is to BCP out all the data from that table.
>Truncate it.  Set the recovery mode to Simple. Alter the table to add the
>column.  Then BCP it back in. You may need a format file to BCP back in
>since the structure will change.  In any case make sure you do a full backu
p
>first and set the recovery mode back to what it was if changed.

Hi Andrew,

I actually used the Alter Table command via QA.  The database is
running on a virtual instance of a SQL cluster.  The machine is a
dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
drives.  Based on perfmon output, the bottleneck is disk IO, and in
fact, the CPU stayed almost less than 10% the whole time.

Actually, we executed the same script on a recently restored copy of
the same database on the other node/virtual instance of the
active/active cluster (so the hardware would be the same).  The script
only took about 30 minutes to finish.  I am thinking that the large
difference in run time may be caused by physical fragmentation on the
production copy of the database.  Will this be possible given the
large time difference?

I will do tests using your recommended BCP approach and see how it
performs on my machine.

Thanks.

Aramid

Best is backup the DB , empty the table and then use alter script to
change the column name and load it back.

-------
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

A 3 disk Raid 5 is the worst Raid you can have when it comes to Writes.
This is especially true f the log file is also on the Raid 5 along with the
data files. Adding a raid 1 for the log files will dramatically speed up
this process. Is the other node using the same Raid array with a different
logical drive or does it have it's own Raid 5?  If they are really two
different ones then you may have a bad disk which will dramatically decrease
both read and write performance. Fragmentation can account for some of the
issues but I wouldn't expect it to be that much.

--
Andrew J. Kelly  SQL MVP


"Aramid" <aramid@hotmail.com> wrote in message
 news:4si5m114t13uq7t
 auetj881ol4vamkoo7b@
4ax.com...
> On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
> < sqlmvpnooospam@shadh
awk.com> wrote:
> 
>
> Hi Andrew,
>
> I actually used the Alter Table command via QA.  The database is
> running on a virtual instance of a SQL cluster.  The machine is a
> dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
> drives.  Based on perfmon output, the bottleneck is disk IO, and in
> fact, the CPU stayed almost less than 10% the whole time.
>
> Actually, we executed the same script on a recently restored copy of
> the same database on the other node/virtual instance of the
> active/active cluster (so the hardware would be the same).  The script
> only took about 30 minutes to finish.  I am thinking that the large
> difference in run time may be caused by physical fragmentation on the
> production copy of the database.  Will this be possible given the
> large time difference?
>
> I will do tests using your recommended BCP approach and see how it
> performs on my machine.
>
> Thanks.
>
> Aramid