Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2009-08-25 : 05:39:37
|
| 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 drive (C:) 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 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-08-25 : 06:01:02
|
| How big is your table on the disk? There is a way to do this (assuming the 2.81GB is enough space to hold the PK [although it will not give you a lot to play with to add more data to the database]). Do you only have the C: drive on this machine? If so, then you will probably have issues no matter what you do, but if you have another drive, create a filegroup on the other drive and create your primary key in on that drive (this will move the data to the other drive as well).Other than that, the other way to do this is to create another table already with the Primary Key, then copy your data in batches (you'll have to do this and the delete these records out of the original table before inserting the next batch). |
 |
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2009-08-25 : 06:10:40
|
| The size of the table is : 42,000 MB.Index space: 0.086MBOk, for the second option I did create an empty table and set the column to primary key but when I imported the data the same problem of C drive with low space issue came about. I would like to try the first option. I have another drive so when you say filegroups what exactly do you mean? Please explain for me abit more.Is there a way to temporarily stop the growth of the temp files, so the column is set to primary key ? Thanks |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-08-25 : 06:18:19
|
| Look up ALTER DATABASE, but the basic syntax is:'ALTER DATABASE AdventureWorks ADD FILE ( NAME = test1dat3, FILENAME = '''+ @data_path + 't1dat3.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),( NAME = test1dat4, FILENAME = '''+ @data_path + 't1dat4.ndf'', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)TO FILEGROUP Test1FG1'--Or you may want to move tempdb to your other drive, which also may help if you have more space on your other drive, have a look at the alter database command in BOL.TempDB will always expand as this is where SQL does its work when moving data and objects around. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-08-25 : 07:06:19
|
Pro tem you may be able to get away with a nonclustered primary key:ALTER TABLE dbo.o_patADD CONSTRAINT PK_o_pat PRIMARY KEY NONCLUSTERED (o_pat_id) |
|
|
|
|
|
|
|
|
Set a Column as Primary Key