Could not create constraint. See previous errors

SQL Server Administration
1

I've had this happen twice now. Today's issue is in an SProc that has been running daily, unmodified, since 2013.

Its an SProc doing an UPDATE on a large table (10M+ rows) and updating between 1,000 and 10,000 rows. We have z dozen or so of these SProcs, part of an overnight refresh from Staging tables, and 3 months ago this arose on one of the other SProcs. I put an Index Rebuild and Update Statistics IN the SProc, to run before the Update, and that cured it. The other Sprocs (similar size table and number of updates) were find, but a couple of days ago another one started giving this error too.

The client has no routine maintenance of their system, no automatic index / statistics rebuilds, and I have no sympathy ... but I have only so much breath with which to lecture them ....

The error line number refers to a CREATE TABLE ##TEMP statement, with a Primary key definition [i.e. included within the CREATE TABLE ##TEMP statement, not as a separate constraint creation statement]. Obviously no actual error there as it has been running just fine for 4 years ...

Anyone come across this before? Feels like a bug in SQL itself.

2

Just curious - but are you sure the global temp table doesn't already exist and that the constraint name is unique? Are you defining the constraint name in the table create statement or are you relying on SQL to generate the name?

Is a global temp table really necessary? Do you really need a primary key defined on the temp table?

3

Constraint names must be unique across the entire db. For temp table especially, it's much cleaner to just use a UNIQUE CLUSTERED index and make all columns in the key NOT NULL. That will avoid issues, since index names only have to be unique within a table, not within the entire db.

If you prefer to create a PK constraint in temp, I'd say don't name it yourself, then SQL will automatically generate a unique name.

4

Thanks chaps, I got my head around this in completely the wrong way based on something else that went wrong on this client's SQL box recently.

The Sproc is assuming the ##TEMP does not exist (i.e. not pre-deleting it) so it is entirely possible that it exists (although I can't think why ... and why the nth Sproc in the sequence, each with a uniquely named ##TEMP, would be the one to fail). The logs give no indication that that process failed the previous day (i.e. leaving the ##TEMP open), but the job (a BATCH file) has been failing every night since and not doing anything (first action is to log that it started, and that isn't happening).

I ran it manually and got a whole raft of "Access denied" ...

... there are also a whole raft of SQL DUMP files, and they have messages like:

Login failed for user 'MyDomain\MyServer$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.nnn.nnn]

network backups ( but client insisted as they just want to mirror the whole machine for DR and having local backups was causing massive network load) have also been failing a high proportion of the time - presumably the cause of those SQL DUMP files, and also permissions-related.

An unattended windows update ran shortly after the initial error, and rebooted the server. Dunno if that did something to change permissions, or if the Update was scheduled by their IT people who had been fiddling with other things ...

Anyway, upshot based on your feedback is that I think:

I'm inclined to name the CONSTRAINT (rather than let SQL choose one). I think that IF the error message had given me a NAME for the constraint I would have taken it seriously (given that the ##table, and thus the constraint, should not exist), whereas with a bland "Could not create constraint" I assumed was a side-effect error as clearly it was physically impossible (or so I thought) for a PKey Definition on a ##TEMP Create Table to raise an error [at the point of create table]

I don't understand how this error arose. There should be no problem creating the constraint (unless the table already exists, in which case a) SQL would be allocating a new, unique, constraint name and b) I would have got a "Cannot create - Table Exists" message instead

Am I missing something? (the SQL code is below)

Should not exist, but the code is not checking whether it does, or not. Manually running the SProc gave no error (so if it did exist at that time it certainly doesn't now). I would have expected a "Table exists" error, rather than constraint error

Is it possible that SQL generated the "same" unique key for the Constraint name?

I think so ... but maybe not!

The SProc calls some dynamic SQL involving OPENQUERY and I think a non-global #TEMP table wasn't in scope - but I may be imaging that, perhaps this was a solution to a problem 10 years ago and "we've always done it that way" since then :roll_eyes:

Yes-ish, I want the SProc to fail if there is a conflict on those columns. That would mean that a 3rd party has changed their schema, or the data-transfer has broken in some way. But as Scott said a UNIQUE index would do

Never thought of that, good idea, thanks. I can't believe I actually need a PKey, as such, on this table - just a check for unique-ness

CREATE TABLE ##TEMP_NameUniqueToThisSproc
(
	[CHANGE_DATE]	 datetime NOT NULL,
	[Col1]			smallint NOT NULL,	-- PKey
	[Col2]			varchar(21) NOT NULL,	-- PKey
	[Col3]			int NOT NULL,	-- PKey
	[Col4]			int NOT NULL,	-- PKey
	[Col5]			varchar(31) NULL,
... lots of columns ...
	[LastCol]			int NULL,

	PRIMARY KEY
	(
		Col1
		, Col2
		, Col3
		, Col4
	)

)

Added:

SELECT @@VERSION

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
5

Now got a corruption on MSDB ... so might well be associated with something else.

I wonder if the DR Copy that their IT people are using is mucking about with SQL itself. Or is that sort of "suspend I/O whilst a low level copy is made" 100% robust?

Server: Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040811520 (type In-row data). Parent node for page (1:6547) was not encountered.
Server: Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040811520 (type In-row data). B-tree page (1:12525) has two parent nodes (1:5086), slot 0 and (1:6589), slot 407.
Server: Msg 8934, Level 16, State 1, Line 3
Table error: Object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040811520 (type In-row data). The high key value on page (1:27385) (level 0) is not less than the low key value in the parent (1:5086), slot 0 of the next page (1:12525).
Server: Msg 8934, Level 16, State 1, Line 3
Table error: Object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040811520 (type In-row data). The high key value on page (1:27385) (level 0) is not less than the low key value in the parent (1:5086), slot 0 of the next page (1:12525).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sysjobhistory' (object ID 149575571).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'msdb'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (msdb).