I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

I'm guessing that it's not possible to break this down into modifying fewer rows at a time, so I'm wondering what my options are for making this change to the database. SQL Azure will not let me change the size of the transaction log (limited to 1gb).

I'm guessing that my best bet is going to be creating a new table with the new layout, migrating the data across into that one, deleting the original table and then renaming the new table to match the name of the old table. If that's the case, how is it best to structure those commands?

Scheduled downtime for our system is not an issue currently so this operation can take as long as it needs to.

  • What were you trying to ALTER? Just changing a datatype on a non-key column? Just curious... – SQLRockstar Jul 28 '13 at 17:43
  • Yes, I'm attempting to change the datatype of a non-key column. I also got this error while trying to change a column from NOT NULL to NULL, but got round it for that one by deleting indexes that referenced it, changing the column and then re-adding the indexes. The same workaround doesn't work for this. – Matthew Steeples Jul 28 '13 at 17:45
  • Also, have you tried changing the recovery model on the db just for this transaction? – swasheck Jul 28 '13 at 19:29
  • @swasheck I'm on SQL Azure so can't change the recovery model – Matthew Steeples Jul 28 '13 at 22:14
  • @MatthewSteeples whoops. didn't notice that tag on the phone – swasheck Jul 29 '13 at 0:09

You will want to load your data into a new table, doing this in small batches, then drop the existing table. I put together a quick example using the Sales.Customer table in AdventureWorks, something similar should work for you also.

First, create your new table, complete with the new datatype you want to use:

CREATE TABLE [Sales].[Currency_New](
    [CurrencyCode] [nchar](4) NOT NULL,
    [Name] [varchar](128) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [CurrencyCode] ASC

Then, insert your records and define your batch. I am using 10 here, but you will likely want to use something larger, say 10,000 rows at a time. For 30MM rows I'd even suggest you go to 100k row batch size at a time, that's the limit I typically used with larger tables:

DECLARE @RowsInserted INT, @InsertVolume INT
SET @RowsInserted = 1
SET @InsertVolume = 10 --Set to # of rows

WHILE @RowsInserted > 0

INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
SELECT TOP (@InsertVolume)
FROM [Sales].[Currency] AS SC
LEFT JOIN [Sales].[Currency_New] AS SCN 
    ON SC.[CurrencyCode] = SCN.[CurrencyCode] 
WHERE SCN.[CurrencyCode] IS NULL

SET @RowsInserted = @@ROWCOUNT

I usually do a sanity check and verify the rowcounts are the same before cleaning up:

SELECT COUNT(*) FROM [Sales].[Currency] 
SELECT COUNT(*) FROM [Sales].[Currency_New]

Once you are confident you have migrated your data, you can drop the original table:

DROP TABLE [Sales].[Currency]

Last step, rename the new table, so that users don't have to change any code:

EXEC sp_rename '[Sales].[Currency_New]', '[Sales].[Currency]';

I don't know how long this will take. I'd suggest you try doing this when you have a clear maintenance window and users aren't connected.


  • That looks to be what I'm after. It was the batch copying of rows that I thought was going to be the hardest. I've got a copy of production that I can run this through for timings etc. Thanks – Matthew Steeples Jul 28 '13 at 22:23

I'm on a phone right now but I just answered a very similar question on StackOverflow a few days ago. The column names are probably not applicable to you, but here's what I gave.

INTO dbo.[News-News_Newest]
FROM [dbo].[News-News];

DROP TABLE [dbo].[News-News_Newest];

EXEC sp_rename '[News-News_Newest]' , '[News-News]';


The same caveat applies to you as we'll. script out your foreign key relationships too and then rebuild the. After the load with check check

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.