MSFT-MSO: ALTER TABLE ADD CONSTRAINT PRIMARY KEY failed after 3 hours with constraint name already exists - by Narayan_Iyer

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 417947 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/25/2009 8:22:58 AM
Access Restriction Public


I was trying to duplicate a table of size over 150 Gigs by BCP’ing it out from a different server and BCPing it in on the problem server with a different name. The idea was to duplicate the table with a different name, create similar indexes and swap the table names to workaround a data issue.

The BCP (out and in) went successful and I used the following SQL to create the clustered indexes on the table:
alter table [dbo].[_IndividualTxlat] add  constraint IndividualTxlatPK primary key clustered 
      [IndividualTxlatID] asc
)with (
 pad_index  = off
,statistics_norecompute  = off
,sort_in_tempdb = on
,ignore_dup_key = off
,online = off
,allow_row_locks  = on
,allow_page_locks  = on
,sort_in_tempdb = on
,fillfactor = 90) 
ON [WWODSystemFG];

The above command ran for 2 hours (the table has over 1 billion records) and failed with:

Msg 2714, Level 16, State 4, Line 2
There is already an object named 'IndividualTxlatPK' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

I’m pretty sure there was absolutely NO blocking on the session creating the index as the base table was completely unused at that point (created with a different name, app will not see it).

I’m assuming that the alter table builds the index pages, check for data integrity on the column that the primary would be created and then tries to add a constraint – where it sees a duplicate name, and fails – just my quick guess, maybe I’m wrong.

Running for 3 hours and failing like this is kind of frustrating. :-) And, not to forget the rollback effort/time of 4 hours. 

I think it would be more appropriate to check for the constraint name and throw the error upfront. Please let me know if you need help with repro. 
Sign in to post a comment.
Posted by Microsoft on 5/26/2010 at 6:43 PM
Dear Customer,
This issue has been fixed in SQL Server Service Pack 2.

Thank you
Posted by Microsoft on 7/7/2009 at 10:45 AM

Just a quick update on the issue you filed.
The issue is now resolved and a fix has been checked into the SQL dev branch. It will be available in the SQL 11 release. I am currently checking to see if it will also make it into SQL Server 2008 R2 due out later this year.

Let us know if you nee dthe fix more urgently.


Posted by Microsoft on 3/10/2009 at 9:41 AM
Thanks, Narayan.
We will be in touch.


From: Narayan Iyer
Sent: Monday, March 09, 2009 7:06 PM
To: Joachim Hammer
Subject: RE: Microsoft Connect: Comment or Request for Information for Connect Feedback ID 417947

Thanks for your response, Joachim.

This is not critical for me and this can wait. Please let me know if you need any additional info to repro the problem.

Narayan Iyer
Posted by Microsoft on 3/9/2009 at 11:49 AM
Dear Customer,

Thanks for reporting this problem to us for your clear description of the issue. We are currently in the process of prioritizing the next set of issues to be addressed in SQL Server Service Pack 2 (SP1 is close to being released). If this is a critical problem requring a fix sooner than that, we recommend that you contact customer support services (CSS) who can assit you in requesting a hotfix.

We will update you periodically on the status of the investigation and when/how a fix will be available.


Joachim Hammer

Program Manager
SQL Server