Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers

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,
 CONSTRAINT [PK_Currency_New_CurrencyCode] PRIMARY KEY CLUSTERED 
(
    [CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

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
BEGIN       

INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
           ,[Name]
           ,[ModifiedDate])
SELECT TOP (@InsertVolume)
       SC.[CurrencyCode]
           ,SC.[Name]
           ,SC.[ModifiedDate]
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
END

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]';
GO

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.

HTH

share|improve this answer
    
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
add comment

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.

SELECT 
[ID],
[Title],
[Abstract],
[Value],
[UserID],
[GroupID],
[Date],
[Views],
[Hates],
[Likes],
[Source]
INTO dbo.[News-News_Newest]
FROM [dbo].[News-News];

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

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

ALTER TABLE [News-News]
  ADD CONSTRAINT PK_News_ID PRIMARY KEY CLUSTERED (ID ASC);

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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