August 29, 2016 at 12:16 pm
I must be missing something. At first it was sounding as if you were getting the error on the SESSION table. Now, based on your latest post it is on a session history table. Could you clear this up for us please?
August 29, 2016 at 8:18 pm
If there would be the same identity generated an error would happen on insert to SESSION table.
Because of a primary or unique key on the IDENTITY column in table SESSION.
Oh, wait a minute - it probably does happen!
How do you know the insert has succeeded?
Do you handle errors in your code?
No, you don't.
So, if the insert into SESSION fails then the next insert into _LOG_SESSION will use @gdGUID which never got inserted into SESSION and the SCOPE_IDENTITY() from the previous IDENTITY insert.
Can you fund duplicate entries in identity column in table SESSION?
_____________
Code for TallyGenerator
August 30, 2016 at 12:58 am
@matt-2 Miller (#4): no
@sergiy, @Lynn Pettis: because the SESSION's row are delete few time after, you can see this on code. When a new row is inserted the previous is generally already deleted. Then when id is duplicate it can insert into SESSION, but fails on SESSION_HIS (that has all records) when the trigger on SESSION run on second duplicated identity. I have a row on SESSION and one on SESSION_HIS with same identity but different GUID (then 2 different row inserted into SESSION).
I think that there is some other code the works on these tables that has not been documented. But nothing found until now 🙁
If you have other ideas on how log this to understand what happens you are welcome 😉 Remember that it can runs for many time until the error happens, so I want to avoid resource intense logging/tracking
Thanks for your suggestions.
August 30, 2016 at 2:46 am
Pivot-156509 (8/30/2016)
@Matt Miller (#4): no@sergiy, @Lynn Pettis: because the SESSION's row are delete few time after, you can see this on code. When a new row is inserted the previous is generally already deleted. Then when id is duplicate it can insert into SESSION, but fails on SESSION_HIS (that has all records) when the trigger on SESSION run on second duplicated identity. I have a row on SESSION and one on SESSION_HIS with same identity but different GUID (then 2 different row inserted into SESSION).
Deleting a row does not change the status of IDENTITY on the table.
I think that there is some other code the works on these tables that has not been documented. But nothing found until now 🙁
You're on the right path.
Look for "RESEED" in the code.
Must be somewhere near DELETE from SESSIONS.
_____________
Code for TallyGenerator
August 30, 2016 at 7:59 am
Sergiy (8/30/2016)
Pivot-156509 (8/30/2016)
@Matt Miller (#4): no@sergiy, @Lynn Pettis: because the SESSION's row are delete few time after, you can see this on code. When a new row is inserted the previous is generally already deleted. Then when id is duplicate it can insert into SESSION, but fails on SESSION_HIS (that has all records) when the trigger on SESSION run on second duplicated identity. I have a row on SESSION and one on SESSION_HIS with same identity but different GUID (then 2 different row inserted into SESSION).
Deleting a row does not change the status of IDENTITY on the table.
I think that there is some other code the works on these tables that has not been documented. But nothing found until now 🙁
You're on the right path.
Look for "RESEED" in the code.
Must be somewhere near DELETE from SESSIONS.
Try running this and see what you get:
select count(*)
from session_his
where sh_id>ident_current('session')
let us know what count you get.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 30, 2016 at 11:13 am
Sergiy (8/30/2016)
You're on the right path.
Look for "RESEED" in the code.
Must be somewhere near DELETE from SESSIONS.
No reseed
Matt Miller (#4) (8/30/2016)
Try running this and see what you get:
select count(*)
from session_his
where sh_id>ident_current('session')
let us know what count you get.
0, but can't be different. The rows inserted in session_his are only by session's trigger.
August 31, 2016 at 8:15 am
Well I've learned the hard way that when I run out of easy answers I need to check that my assumptions hold up 🙂 So mant times for me, the assumption I didn't challenge was the one that didn't hold up.
Assuming you tried the error checking like Sergiy mentioned - I would look at a few additional items (again - just hunting for now - we don't have a root cause as of yet):
- check for any references to IDENTITY_INSERT directives in your code.
- a few additional profiling checks that might help:
select ss_id from session
intersect
select sh_id from session_hist
select * from session_hist where sh_id=-1
edited for typoes
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2016 at 8:39 am
Create a job which every minute records current time and ident_current('session').
When you encounter the issue again - check when the identity was reseeded.
That must give you a hint who's the offender.
no reseed
Did you check all Agent jobs?
What about PowerShell scripts?
Executing SQL from application?
Anything else connects to the database?
_____________
Code for TallyGenerator
August 31, 2016 at 11:04 am
@Matt Miller (#4):Thanks for your help.
I have no identity_insert.
select ss_id from session
intersect
select sh_id from session_hist
ss_id
-----------
2045840
(1 row(s) affected)
This is the duplicated identity.
Next one: sorry, why do you search for -1?
select * from session_hist where sh_id=-1
SH_ID SH_USER_ID SH_GUID
----------- ------------ ------------------------------------
(0 row(s) affected)
@Sergiy:thank you, too.
This happen with agent jobs and until now always outside working hours.
No powershell script.
Application: no sql command. There is an application server, but don't use the involved table and outside working ours it should do anything.
I can log as you suggest, but I need to wait next time.
I have also found that in the same moment another table has identity duplicated. It is the first time in this table, but same time of other. This table have only insert, no generated id and suddenly an insert fails to primary key violation (the identity is PK).
EDIT: log scheduled!
September 12, 2016 at 9:15 am
Pivot-156509 (8/31/2016)
@Matt Miller (#4):Thanks for your help.Next one: sorry, why do you search for -1?
select * from session_hist where sh_id=-1
SH_ID SH_USER_ID SH_GUID----------- ------------ ------------------------------------
(0 row(s) affected)
I was actually trying to see if the trigger's INSERT statement was having issues and had switched the user ID and session ID in the insert from the trigger. I've been bitten multiple times with using "generic" inserts (without specifying columns names) which eventually get messed up because the source and target column order become disaligned.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2016 at 10:47 am
Matt Miller (#4) (9/12/2016)
I was actually trying to see if the trigger's INSERT statement was having issues and had switched the user ID and session ID in the insert from the trigger. I've been bitten multiple times with using "generic" inserts (without specifying columns names) which eventually get messed up because the source and target column order become disaligned.
Ok, yes, there is an insert with * (I got chills when I read 😉 but it is not the cause).
February 28, 2017 at 4:11 pm
Check this out. Seems to be the cause of this issue and it is fixed in a later version. You should be good if you have upgraded:
https://support.microsoft.com/en-us/help/3011465/fix-sequence-object-generates-duplicate-sequence-values-when-sql-server-2012-or-sql-server-2014-is-under-memory-pressure
March 4, 2017 at 2:58 am
sabby91 - Tuesday, February 28, 2017 4:11 PMCheck this out. Seems to be the cause of this issue and it is fixed in a later version. You should be good if you have upgraded:https://support.microsoft.com/en-us/help/3011465/fix-sequence-object-generates-duplicate-sequence-values-when-sql-server-2012-or-sql-server-2014-is-under-memory-pressure
Thank you, but it was not a sequence object, it is an identity. Anyway no more happened, I am waiting for it 😉
April 6, 2017 at 1:57 pm
I hit the same issue and was searching for some history on it. Unfortunately, I lost all the links I read to form the conclusion except for the final link I copied here. However, found a few of them back.
We have reply from Microsoft stating that IDENTITY saw a rewrite in SQL Server 2012.
Another reply that says IDENTITY internally uses the sequence mechanism.
http://dba.stackexchange.com/questions/122254/sudden-primary-key-violation-on-identity-column
I dont know if this qualifies as confirmation of the issue. But I have seen the same behaviour where the issue doesnt appear with the update, but is seen without it.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply