Very often the case that EM will do in a long and convoluted way something that takes one line and one minute in QA.
-----Original Message-----
From: Steve Willoughby
[mailto:mssqldba-ezmlmshield-x63691577.[Email address protected]
Sent: Friday, October 28, 2005 10:56 AM
To: LazyDBA Discussion
Subject: RE: Big Problem changing datatype in Tables with Many Rows
Hi Tony,
That sounds reasonable (and much more simple than the script em
generated!) We just took the script at face value, but we've learned a
valuable lesson here!!! When dealing with millions of records in fairly
wide tables with several indexes, in the future we will think before we
click.
Steve
-----Original Message-----
From: Tony White
[mailto:mssqldba-ezmlmshield-x6209427.[Email address protected]
Sent: Friday, October 28, 2005 10:41 AM
To: LazyDBA Discussion
Subject: RE: Big Problem changing datatype in Tables with Many Rows
[bcc][heur]
Importance: Low
I might be being dense here, but can't you just use:
Alter TABLE TABLE1 ALTER COLUMN COL2 bigint
Carol Green <mssqldba-ezmlmshield-x96748619.[Email address protected]
wrote:
Isn't the change from int to bigint implicit (i.e. you can just change
the datatype of the column and the conversion will be done
automatically) or am I missing something (probably)?
-----Original Message-----
From: Steve Willoughby
[mailto:mssqldba-ezmlmshield-x51749924.[Email address protected]
Sent: 28 October 2005 15:14
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
This email and any files transmitted within it are private and
confidential.
If you are not the intended recipient, this email and any attachments
within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using
the information contained within this email.
Nothing in this email message amounts to a contractual or legal
commitment on the part of Optilan unless confirmed by a communication
signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from
viruses. Although every possible care is taken by Optilan, Optilan does
not accept any liability whatsoever for any loss or damage which may be
caused as a result of the transmission of this message by email.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
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