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)
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.