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.
Obviously the amount of free space on your C-Drive is too low.
I'd advise you to move the database/s to another partition or disc (such as a D-Drive) with more storage space available. This also avoid problems with the OS if it's located on the C-Drive including it's swap file; in my experience having little room for swapping doesn't do too well for the OS.