-1

Prod Env: SQL Server 2014 sp2

Test Env: SQL Server 2014 sp3 CU4

Prod and Test matches up. Recently updated Test Env to sp3 cu4. Since updating proc_a (within a SQL job) fails due the below error. Proc_a is using a cursor to insert into table_a. The errors are legit, there are duplicate records and I know how to handle the duplicates.

I want to understand why prod is working and test isn't. Suggestions?

Looking through the release notes, nothing sticks out.

SQL SERVER 2014 SP3 https://support.microsoft.com/en-us/topic/kb4022619-sql-server-2014-service-pack-3-release-information-64f341d3-2b74-0d86-e857-b9fd0775e493

SQL SERVER 2014 SP3 CU4 https://support.microsoft.com/en-us/topic/kb4500181-cumulative-update-4-for-sql-server-2014-sp3-a55b2271-fdae-8e82-115c-05214cc8dac0

ERROR: Msg 2627, Level 14, State 1, Procedure proc_a, Line 80 [Batch Start Line 7] Violation of PRIMARY KEY constraint 'PK_table_a'. Cannot insert duplicate key in object 'dbo.table_a'. The duplicate key value is (37, 56500909580000).

11
  • when you say prod works, what does that mean? it does not attempt inserting duplicate keys or they get inserted without error. you have a different build of SQL between prod and test, what else is different (cpu,mem, sp_configure)? could plan be parallel on one server and single thread on the other?
    – Bob Klimes
    Mar 30, 2022 at 13:34
  • @BobKlimes - Prod sql job running proc_a runs nightly no problem, inserts into table_a without dups. Test sql job errors out randomly, 95% fails. Parallel could cause issue? PROD: 8 cpu, 32gb mem, 50 ctfp , 4 mdop. TEST: 4 cpu 49gb mem , 5 ctfp, 0 mdop.
    – jay
    Mar 30, 2022 at 15:05
  • you could try running proc in test with maxdop 1, to see if it still fails.
    – Bob Klimes
    Mar 30, 2022 at 15:27
  • @BobKlimes - Thanks for the suggestion. Test proc exec plan shows parallelism , however tried maxdop1 and got the same results.
    – jay
    Mar 30, 2022 at 18:20
  • 1
    Is it possible in your production server, you're using the IGNORE_DUP_KEY option on the indexes of your table? See this DBA.StackExchange post also.
    – J.D.
    Mar 30, 2022 at 19:09

2 Answers 2

0

Have you checked your target(Job Properties>Targets) on the job? If they are in the same domain you might just be executing the job against the Prod server again rather than the Test Server. Other thing to check is the schedule. Typically people like to schedule their jobs (like hourly) on Test Servers more often than Prod, so perhaps there might be a reason in the T-SQL of the sproc that only causes duplicates when it's ran back to back rather than nightly?

3
  • Thanks for the feedback. The source table for the insert has duplicates in both environments so I am expecting primary key constraint errors. The Prod env runs to completion , Test errors out with the errors I posted. I was told Test worked before it was updated with SP3 CU4.
    – jay
    Mar 31, 2022 at 12:08
  • Are there any trace flags enabled? Run dbcc tracestatus(). Another thing is check for any triggers in the table.
    – BAllen
    Apr 1, 2022 at 2:00
  • No trace flags, no triggers on tables. thanks for feedback.
    – jay
    Apr 1, 2022 at 13:39
0

Turns out it the root cause was a combination of Compatibility level and SQL level. SQL Server 2014 sp2 and Compat level 100 combined ignores(suppress errors) "Msg 2627, Level 14, State 1".

TEST: SQL Server 2014 sp2 and Compat level 100 combined. PROD: SQL Server 2014 sp3 CU4 Compat level 120.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.