Alter table alter column running long and filling log

Question

Alter table alter column running long and filling log

Jeff Sims-413169

Say Hey Kid

Points: 711
More actions
May 24, 2016 at 11:46 am

#316730

I am running a alter table tablename alter column columnname varchar(66) on a very large table, currently is INT. 1billion rows. Ran 11 hours but filled our logs before we could expand the luns. Any ideas why it would log 600gb worth of log during the transaction on a 75gb table? And any suggestion on things to look at to improve its performance or stop that logging? We got rid of all indexes with the column, deleted all auto created stats and turned off auto_create_stats for the time it ran. Anything is appreciated...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Jeff Sims-413169 (5/24/2016)
I am running a alter table tablename alter column columnname varchar(66) on a very large table, currently is INT. 1billion rows. Ran 11 hours but filled our logs before we could expand the luns. Any ideas why it would log 600gb worth of log during the transaction on a 75gb table? And any suggestion on things to look at to improve its performance or stop that logging? We got rid of all indexes with the column, deleted all auto created stats and turned off auto_create_stats for the time it ran. Anything is appreciated...

Quick thought, it is probably less strain on the server if one creates a new table with the correct col types then does a minimally logged insert from the original table and finally as rename of both.

😎

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Do you have any triggers (especially auditing ones) on the table?

Is the column you are adding NULLABLE?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 3

no triggers. Current type is int, null changing to varchar(66), null

Lowell SSC Guru Points: 323504 More actions · Answer 4

i would think do the following

add a new column, ie

update it to the equiv orig value, maybe in small batches

rename orig column

rename new column

drop orig column

ALTER TABLE myTable ADD OrigColumnTemp varchar(66)

--batches?

UPDATE myTable SET OrigColumnTemp = CONVERT(varchar(66),OrigColumn) WHERE OrigColumnTemp IS NULL AND OrigColumn IS NOT NULL

EXEC sp_rename 'myTable.OrigColumn','OrigColumnOld','COLUMN'

EXEC sp_rename 'myTable.OrigColumnTemp ','OrigColumn','COLUMN'

ALTER TABLE myTable DROP COLUMN OrigColumnOld

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!