February 28, 2017 at 2:36 pm
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...
February 28, 2017 at 3:29 pm
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply