Remove indexes and set the recovery mode to minimal logging.
Then, small transactions, say 10-20K rows at a time.
Then rebuild indexes, then set recovery mode back to whatever it was.

Just for interest's sake, what was the EM script doing?

-----Original Message-----
From: Steve Willoughby
[mailto:mssqldba-ezmlmshield-x51749924.[Email address protected]
Sent: Friday, October 28, 2005 10: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