I've recently dealt with this scenario in an environment with 500M rows on transaction tables in production, with no down-time. The process is not simple, but it does the job beautifully, without incurring down-time.
The gist is using several tricks together:
1st, I play a shell-game: I create two tables with the new structure - let's call the original table Original, and the two new tables newOriginal and tempOriginal.
In the shell-game, I rename Original to oldOriginal and tempOriginal to Original - what this step accomplishes is to get a table with the new structure immediately handling any requests directed at this table. A bit of trickery here is that with every rename step, you must run sp_depends on the objects, capture its output, and recompile everything that references the renamed objects, so that, for instance, stored procedures start looking for the newly named object.
Now that there is a repository to keep the data flowing in the right direction in normal usage, I move the data from the "oldOriginal" table to the "newOriginal" object. The trickery here amounts to doing this through a cursor on a row-by-row basis, with commits issued on a timer, and interlaced rests - so, for instance, I set a time-mark, copy rows for 2 seconds, and sleep for 5. IN actuality I do more work than this because I have the loop tuning its wait periods based on the number of blocks happening at the end of each interval (in sysprocesses) and their wait-time.
When this copy to the new structure is done, I rename the tables once again, this time naming "Original" (the temporary table) back to "tempOriginal", and "newOriginal", the new structured table with all the added data, to "Original". At this point, I do another sp_depends recompile. Finally, I copy all the rows that might have been added to "tempOriginal" in the interim, and move them to the new "Original" table, and I'm done, with no interruptions in service and no down-time!
This process, once worked out, is quite smooth. The main tricks are to run the recompile of anything exposed in sp_depends (views or procs), and to commit every so often, because the rollback you can incur can be just as long as the pickle that made you roll-back in the first place.
Anyway, it's not a quick and dirty solution, but it works great on larger data sets in heavy transaction environments, and can be used for vertical table partitioning with great results...
Best,
Juan Cristián
-----Original Message-----
From: Steve Willoughby [mailto:mssqldba-ezmlmshield-x51749924.[Email address protected]
Sent: Friday, October 28, 2005 7:14 AM
To: LazyDBA Discussion
Subject: Big Problem changing datatype in Tables with Many Rows
We have to change a field datatype in 3 different tables from int to
bigint. The tables have 5 million, 25 million, and 18 million rows.
Initially, we tried using a script generated by EM for each table that
copied the data to a holder table, recreated the table with the new
datatype, and inserted the data back into the new table, etc.
Well, the table with 5 million rows finished in a few hours, but the one
with 25 million rows has been going for about 36 hours with no end in
sight and the table with 18 million rows hasn't even started. You can
imagine the user community is a little miffed right now.
We've stopped the script that was changing the 25 million row table, but
it's now in massive rollback mode.
There's got to be a better way! Any suggestions on what we can do now to
mitigate the damage would be greatly appreciated!!!
Steve
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page