SQL Server Performance Forum – Threads Archive

Set primary key after millions of records had add

I made a huge mistake when didn’t check if the table has primary key before bulk insert records in it. Right now, it has about 3 millions records. Is there any way to set the primary keys without taking the server down? please help… Lily

Use TSQL, I don’t see why you will need to take the whole server down. Word of caution, just like with any mass updates or table modifications run this during a low user activity window. ALTER TABLE My_Table ADD CONSTRAINT PK_Name PRIMARY KEY (my_pk_Column) Raulie
hp

I meant when I ran the update (set primary key), the database is blocking up all resources. The sql server went down.

Is this a 24×7 system or do you have off-peak hours where you can let run a scheduled job which does the UPDATE? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

If you have other databases on the same server then why not detach the database on production server, move it to a development server, add the PK there, then attach again on the production. That should take the load off the production server.

I’ll do in the dev. box and move it back in production.
Thanks every one for your help. You’re so kind.
Lily

If you’re unsure about the change it is always suggested to test the process on the dev. box in order to face and resolve any issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>