5

Due to one of our tables growing outside of the range of an INT, something that wasn't predicted when the DB was produced, we're need to change the PK field to a BIGINT instead.

We are however running into problems regarding tempdb space usage at the very first step while handling that table: trying to drop the primary key constraint on it so we can alter it. None of our test environments have more space than 50 GB reserved for tempdb, while the table takes up around 120 GB of space right now. The table has but 5 columns and they contain only other INTs and a few short varchars.

Do any of you have any better, perhaps lighter suggestions where tempdb and logfile are concerned, to do the same changes? The table has only a few columns, all of which contain very little data. Sample below, as simple as it may be.

ALTER TABLE OURTABLE DROP CONSTRAINT OURTABLE_PK

ALTER TABLE OURTABLE ALTER COLUMN OURTABLE_ID BIGINT NOT NULL

ALTER TABLE OURTABLE ADD CONSTRAINT OURTABLE_PK PRIMARY KEY CLUSTERED (OURTABLE_ID)

Thanks!

I've been thinking about just creating a new table with the same definitions and a BIGINT as a bulk insert, then renaming it while dropping the old one. But I'm unsure whether that will prove to be any better a solution since the constraints would have to be put in afterwards anyway.

Improve this question
  • 1
    Have you considered moving tempdb to a larger drive temporarily? Perhaps an SSD? This would make the operation quite a lot faster, I believe. – Max Vernon♦ Nov 22 '13 at 5:38
  • Assume that we have no control over these settings and that other solutions will be asked to be looked into first. There's one environment where I can temporarily force the tempdb on the same disk as the db itself, but that'll also overlap the IO costs. That's probably what I'm going to have to do next though, so thanks for the suggestion. :) – Kahn Nov 22 '13 at 5:46
6

Assuming you have a maintenance window that allows for a short period of downtime I would suggest using BCP to dump the table to a file. If space is an issue, compress the target folder in advance of the export.

bcp.exe "Database.dbo.OurTable"  OUT "C:\Temp\bcp\OurTable.dat" -S ServerName -T -c -r "|¬|\n" -t "|¬|" /b 10000

DROP your old table, CREATE new with BIGINT key, then import the data.

BULK INSERT 
    dbo.OurTable
FROM 'C:\Temp\bcp\OurTable.dat'
WITH
(
    FIELDTERMINATOR = '|¬|',
    ROWTERMINATOR = '|¬|\n',
    BATCHSIZE = 10000,
    ERRORFILE = 'C:\Temp\bcp\OurTable_error.txt'
)

The odd looking choice of delimiter |¬| is a typically safe combination that avoids collisions in text fields. So far anyway.

As you mention in the comments you can minimise growth of the log by switching to bulk-recovery. Given that you will have no other activity during this maintenance window you could switch to simple, run the process, switch back to full and take a full backup instead. Note that use of BATCHSIZE in the example will create a separate transaction for each batch.

Improve this answer
  • Seems like a great idea. Apparently I also need to put the db into bulk-logged recovery mode to avoid facing the same problem with the transaction log though? – Kahn Nov 22 '13 at 10:57
  • 1
    Marking this as accepted. Sorry about the delay. I had a load of other projects and tickets to deal with while doing this one. The bulk export / import easily did what it was supposed to in a matter of less than 10 hours, and solved a whole plethora of other problems with regards to logging and tempdb filling in the process. Thanks! The full changes aren't done yet but they are a separate problem. – Kahn Dec 4 '13 at 8:10
0

Kahn,

I am in the same boat as you on a table with 100 million rows sizing to 120 GB. What did you do eventually? I do not plan to create a BCP

Improve this answer

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.