Set a Column as Primary Key

[dr223]

Hallo,

Ok, I'll try to be as clear as possible. I am working with SQL server 2005 which has been installed on a server and the C Drive where SQL is placed has a free space of 2.81 GB.

I have a table called dbo.o_pat, which is built up of nearly 110,000,000 records. The table was not indexed initially when loaded to SQL. Now, my primarily responsibility is to index a column called o_pat_id as Primary key. Therefore, I right click the column and option "Set as Primary Key". The process starts to set the respective column as primary key but the problem is tempdb.mdf and templog.ldf (system files) size start increasing very rapidly, within 30 or so seconds a message crops the disk space is C drive is low on the right hand corner of the screen ....If I leave it to run further it crashes and the column is not set to primary key...

Then, I restart the server and tempdb.mdf and templog.ldf reduces from GB to KB in size (its original size). My space then returns to 2.81 GB again.

Please can anyone advice me what I can do to set the primary key with the 2.81 GB space still left in the C Drive if possible.

Your response will highly be appreciated.. Thanks

 

[DCrake]

Not an immediate solution, but I would suggest you get some more disk space as < 3mb is very small what is the total capacity of your drive?

David

 

[dr223]

The total size is 14.9 GB, 12.1 GB has been used what is left is 2.81 GB. I have tried to convince my manager for more space, but the process is long and the table is used by an application and since it is not indexed it is very slow to extract records.

No ideas, maybe to temporarily stop the growth of the tempdb.mdf and templog.ldf while the column is set to primary key.

Thanks

 

[SQL_Hell]

Got any other drives on this server? with more space on them?

 

[Banana]

An alternative is to create a new table with the primary key set, then run a series of INSERT queries to transfer content from one table to other table then delete the same batch from original table & shrink files if needed.

It's more risky because if something goes wrong in between the ends, you would be stuck with two half-tables but if you can save it into a DVD or whatever and use it as a backup, then you can try that to work around the problem of needing more space to wrap everything in a single transaction, which you don't.

 

[SQL_Hell]

An alternative is to create a new table with the primary key set, then run a series of INSERT queries to transfer content from one table to other table then delete the same batch from original table & shrink files if needed.

It's more risky because if something goes wrong in between the ends, you would be stuck with two half-tables but if you can save it into a DVD or whatever and use it as a backup, then you can try that to work around the problem of needing more space to wrap everything in a single transaction, which you don't.

I was going to suggest this also, but then he will still need to re-index afterwards which will bloat the tempdb again.

Unless the OP transfers the records in small batches and re-indexes everytime.

But honestly with that little disk space left, it isn't going to be long before your server is on it's knees.

Why was it all put on the C drive? its better to have separate drives for logs and data