April 10, 2012 at 10:23 am
This irritates me. I can't figure out why this is happening.
I have 5 tables with identity set on their ID fields. They get used every month for reports, but the data is only updated once or twice a year. Each time I go to update the data, the identity property is off.
These tables are not involved in replication. There is no log shipping. The database does not get restored on top of itself (this is a production report database), and the tables don't get Imported via the SSIS wizard either. And yet, the identity property keeps getting turned off. And I only find out about this when I go to insert the new records and get complaints about the columns not allowing NULLs. This is very frustrating (it's about the 4th time this has happened to me). I cannot figure why it's happening and I'm fairly certain none of the other DBAs are touching the tables.
Does anyone have any thoughts on this?
April 10, 2012 at 10:29 am
Pretty sure it isn't doing it to itself, someone has to be making a change. Your choices going forward are to setup some form of audit (trace, extended events, ddl triggers) to try and capture when it happens.
April 10, 2012 at 10:51 am
I'm with Lynn, this isn't going to happen on its own, something has to DO it. And depending on version of SQL depends on what your options are.
CEWII
April 10, 2012 at 1:01 pm
Brandie, are there any Dynamic SQL somwhere that may alter that table's proprety? Normally, u cant catch if that table name is updated/used in a dynamic sql! I know that u know, but just my 2 cents!
April 10, 2012 at 1:16 pm
DDL trigger?
April 11, 2012 at 11:13 am
I haven't thought about dynamic SQL or DDL triggers. I'm usually the only one touching these tables. I'll have to check those options out. Thanks for the input.
April 11, 2012 at 11:14 am
Elliott Whitlow (4/10/2012)
I'm with Lynn, this isn't going to happen on its own, something has to DO it.
"Something" is different from "someone," and I'm over 95% certain that there isn't another DBA manually touching these tables besides me. If only because none of them know what these tables are for and don't want to deal with the hassle of figuring out the report process.
April 11, 2012 at 11:18 am
I don't know if the two are related, but I've occasionally "lost" calculated columns. The column is still present, but the calculation part of it has disappeared. This doesn't happen as often as the identity issue, though.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply