Violation of PRIMARY KEY constraint

Question

Post reply

Violation of PRIMARY KEY constraint

Benki Chendu

SSCrazy Eights

Points: 8051
More actions
April 17, 2014 at 12:15 pm

#284674

Got the following error when the backup was run
Executing the query "BACKUP DATABASE [msdb] TO DISK = N'd:\\Sql backups..." failed with the following error: "Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB7WERB85D3'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (16771).
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
12 percent processed.
21 percent processed.
31 percent processed.
41 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 5240 pages for database 'msdb', file 'MSDBData' on file 1.
100 percent processed.
Processed 8 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 5248 pages in 0.397 seconds (103.261 MB/sec).
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
How do i fix this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

looks like someone restored msdb ? or maybe actually did a DBCC CHECKIDENT with Ressed?

the diagnosis is easy, and so is the fix:

the diagnosis:

use msdb;

GO

DBCC CHECKIDENT ('dbo.backupset')

SELECT MAX(backup_set_id) + 1 FROM dbo.backupset

if the current identity is less than the max row in the table, that explains the error.

the fix would be as follows:

use msdb

GO

Declare @FixedKey int

SELECT @FixedKey = MAX(backup_set_id) + 1 FROM dbo.backupset

print 'new correct key is ' + convert(varchar,@FixedKey)

DBCC CHECKIDENT( 'dbo.backupset',RESEED,@FixedKey)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MMartin1 One Orange Chip Points: 27768 More actions · Answer 2

hi Lowell,

I'm curious why you need the +1 in the query

use msdb;

GO

DBCC CHECKIDENT ('dbo.backupset')

SELECT MAX(backup_set_id) + 1 FROM dbo.backupset

if the current identity is less than the max row in the table, that explains the error.

From Books online I see

"DBCC CHECKIDENT ( table_name, NORESEED )

Current identity value is not reset. DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values."

and

"DBCC CHECKIDENT ( table_name )

or

DBCC CHECKIDENT ( table_name, RESEED )

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column

"

----------------------------------------------------

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Hmm, never seen that error before. A restore from backup would include resetting the identity value to what it was when the backup was taken, so that shouldn't cause a repeated value.

I'd be concerned about a corrupt msdb so I'd run DBCC CHECKDB against msdb.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I'm with Lowell. This looks to me like somebody did a reseed on backupset.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events