Dropping large clustered primary key fills up tempdb

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.

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.

StackExchange Database Administrators Q#53766, answer score: 6