SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


low identity value inserted


low identity value inserted

Author
Message
dan.lampkin
dan.lampkin
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 15
I have a table that has an identity on it. There are many people and apps that hit this table. the value increments as normal most of the time, but every now and then, we get a low value, as if someone uses and identity insert or reseeds it and it causes issues. So, I'm trying to find out the user that may be doing it. I set up a trigger (this is a non-prod env) to log if a new value is used lower than the max value and it logs to a staging type table. I got a hit and asked the user what they were doing. they just did a manual insert of a couple of rows, no reseed or identity insert. a record was inserted normally and then a day later, this user inserted a record with the low value, so this doesn't tell me he did it, but rather that something happened from the last insert until his insert. I'd like to find a way that will tell me if the identity value changes (e.g. what checkident shows me), but the only way I can think to do it is to have a sproc or job that runs every minute or so, but even then that just tells me that it changed, not how it changed or who did it. Anyone have any ideas of how to track if the identity value (internal) changes? like if I had a ddl trigger that shows when any ddl changes, but something for checkident or something. Or if anyone has any ideas of how else the identity could change? sometimes it goes back to 1 and sometimes it goes to an arbitrary number (e.g. 12345 or something). there is too much data so I know that nobody is dropping and recreating the table or truncating it (there are billions or rows and data would be missing for a while). we have looked through a ton of code and can't find a reseed or identity insert anywhere, but so many people hit it we could easily miss it somewhere...


Any suggestions would be appreciated...
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33642 Visits: 9596
Since your on 2008, one option you might want to look at is setting up a server side trace and just limit it to SQL:BatchCompleted and filter the TextData column on SET IDENT% and DBCC CHECKIDENT%. Capture columns like TextData, application, session, user/login columns, start time and whatever else. With the frequency you've seen, it wouldn't have much, if any, of an impact and you could probably figure out who or what is causing this.

Sue




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search