May 30, 2013 at 8:39 pm
I have a situation that's driving me INSANE...
The vendors of our financial processing system came to us asking that we increase a column in a table from char(15) to char(19) to allow for extra room in a needed field from the web application. Typically this type of change wouldn't such a big deal, however this table has about 90 million rows in it and has a data footprint of about 214GB.
Now unless I'm missing something (which is very possible, as I'm no expert at anything), to issue an ALTER COLUMN like this via SSMS it involves the creation of a new "temp" table, transfer the data from the existing table to the new table, create all non-clustered indexes on the new table, and drop the old table, rename the new.
Consider the command:
A-LTER TABLE MyProblemChild A-LTER COLUMN
[FleetCardVehicleNo] char (19) NULL
GO
I did this on a testing server with attached raided storage and it took about 1.5hrs and the log file was pre-sized at 230GB - not too bad
When I attempted to run the same process on our production server (which has the data, index, and log files on a SAN, each residing on their own respective LUNS), the process ran for over 5 hours, grew the log file to over 490GB...and only stopped there because the LUN ran out of space... to which I had to kill/rollback the entire thing!
How can I calculate how much log file space will be needed to successfully complete the operation?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 30, 2013 at 9:02 pm
I forgot to mention: on the production server, transactional replication and log shipping is set up (which is not running on the other DB were I was successful running the A-LTER script...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 4, 2013 at 7:16 am
This is interesting - When you alter a column i.e.(increase the size) in SSMS using the GUI in it creates a temp table, locks the temp table, inserts all the data into it from the original table, drops the original table, creates all constraints, then creates any non-clustered indexes, and finally it drops the original table.
If you do the same thing via TSQL a profile trace shows something completely different:
UPDATE [MYDB].[dbo].[MyProblemChild ] SET [FleetCardVehicleNo] = [FleetCardVehicleNo]
Does anyone know if this is doing the same thing as the GUI "under the covers" so-to-speak or is this literally updating the value in one column to the value in the next? If the latter, it would explain why this statement runs like a champ on the server with no replication, but runs for hours on the server that does have it set up, and blows up the transaction log (as it would be also generating millions of replication commands to send over to the distributor/subscriber).
If this is the case should I just manually script out the way it's done via SSMS?
Any thoughts?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 4, 2013 at 7:30 am
MyDoggieJessie (6/4/2013)
This is interesting - When you alter a column i.e.(increase the size) in SSMS using the GUI in it creates a temp table, locks the temp table, inserts all the data into it from the original table, drops the original table, creates all constraints, then creates any non-clustered indexes, and finally it drops the original table.If you do the same thing via TSQL a profile trace shows something completely different:
UPDATE [MYDB].[dbo].[MyProblemChild ] SET [FleetCardVehicleNo] = [FleetCardVehicleNo]Does anyone know if this is doing the same thing as the GUI "under the covers" so-to-speak or is this literally updating the value in one column to the value in the next? If the latter, it would explain why this statement runs like a champ on the server with no replication, but runs for hours on the server that does have it set up, and blows up the transaction log (as it would be also generating millions of replication commands to send over to the distributor/subscriber).
If this is the case should I just manually script out the way it's done via SSMS?
Any thoughts?
The GUI generates a script that creates the new table, moves the data, drops the old table, renames the new table, creates all the constraints and nonclustered indexes. You can actually have SSMS show you the script.
If you just use the ALTER TABLE ALTER COLUMN to change the size of the column, none of this is done.
June 4, 2013 at 8:37 pm
Thanks Lynn, yes I was aware of this occurring when you do it via SSMS but I was more curious as to why the same thing doesn't happen when you specify ALTER TABLE via TSQL?
I did some testing today and did find that when I used TSQL, an UPDATE occurs...this update appears to then process each row one by one, and in a table with 50 million rows, it blew up a 240GB log file in minutes!
Taking the approach of SSMS, I scripted out the TSQL and modified it to suit my needs, dropped the table/article from replication, ran the commands and while there were a lot of steps involved, it worked very well and virtually left the log file empty.
I need to test how the log files reacts when issuing the ALTER TABLE command directly from TSQL, dropping the replication article, and see the impact on the log files.
Appreciate the feedback!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 4, 2013 at 8:50 pm
MyDoggieJessie (6/4/2013)
Thanks Lynn, yes I was aware of this occurring when you do it via SSMS but I was more curious as to why the same thing doesn't happen when you specify ALTER TABLE via TSQL?I did some testing today and did find that when I used TSQL, an UPDATE occurs...this update appears to then process each row one by one, and in a table with 50 million rows, it blew up a 240GB log file in minutes!
Taking the approach of SSMS, I scripted out the TSQL and modified it to suit my needs, dropped the table/article from replication, ran the commands and while there were a lot of steps involved, it worked very well and virtually left the log file empty.
I need to test how the log files reacts when issuing the ALTER TABLE command directly from TSQL, dropping the replication article, and see the impact on the log files.
Appreciate the feedback!
Just as I said, the GUI generates all that code for you. If you simply use ALTER TABLE, the SQL engine is not going to generate all that code that the GUI generates.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply