June 17, 2014 at 3:23 am
One of my database transaction log backup jobs failed this morning, although the log backup was still taken, the job reported failure. The job has run successfully a number of times since. The job reported a primary key violation error on the msdb.dbo.backupmediaset table. The full error message was
Violation of PRIMARY KEY constraint 'PK__backupme__DAC69E4D599F1693'. Cannot insert duplicate key in object 'dbo.backupmediaset'. The duplicate key value is (401862).
Msg 3009, Level 16, State 1, Server myservername, Line 1
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.
After investigating the backupset and backupmediaset tables in msdb I can see that value used for the failed insert was used previously for the last database log backup in the previous log backup run. The failure was reported for the first database log backup attempted. The SQL Log shows an entry for the log being backed up successfully.
I have checked the identify value in the backupmediaset table using DBCC CHECKIDENT and all looks OK.
DBCC CHECKIDENT ('backupmediaset',NORESEED)
Has anyone seen this before, and is there anything else I need to check?
June 17, 2014 at 4:55 am
Did backup configured through maintenance plan? If not create it with maintenance plan and check.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 17, 2014 at 5:02 am
The backup is configured using a SQL Server Agent Job calling Ola Hallengren's DatabaseBackup stored procedure. The job has run every 30 minutes for months without any issue until today, and, so far, has been OK since.
I'm not sure what help creating it as a maintenance plan would be. Can you explain further please?
June 17, 2014 at 5:35 am
Maintenance plan may avoid such duplicate entry.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 17, 2014 at 5:36 am
How?
June 17, 2014 at 11:13 am
I prefer NOT to use maintenance plans. Much more flexibility using procedures like Ola's (or home grown ones) to accomplish database maintenance processes.
June 18, 2014 at 3:21 am
I agree. I also find it much easier to deploy jobs using scripts to multiple servers compared to maintenance plans.
Well the transaction log backup job has been running OK since this little blip yesterday. So, I think I shall have to put this down to some bug in SQL Server. I believe the insert into backupmediaset table is carried out by the database engine itself once the backup job completes so there is no code I can take a look at to find out what happened.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply