7076 - Referenced field is in a transaction even after database restart

[former_member604454]

I have a record that is referenced by several tables. Even after a database restart, I get a 7076 RI rule error when I try to add records to any of the child tables. How can I fix this?

ADS 11.1.0.49

[michael_loop]

Adtfix will check each table in a specified folder, so you could move or copy the suspect table to a new folder before checking it. Be sure to take the option to make a backup before checking.

I know you are using ADS 11, but for the benefit of other readers, ADTFix is not yet compatible with ADS 12.

[former_member604454]

So backt o this one since my client really needs this file, I am assuming the record is corrupted and the transaction bit is set. The ADTFix utility notes mention it will fix "Records that have never been released from a transaction" but do you know if I can run the adtfix on one table, or honestly even one record, rather than the whole database? It's just so slow and this database is very large.

[michael_loop]

You should be able to delete TPS files that are not in use. These would be orphans from a server crash. I would hope that files that are in use by the server would not be deletable, although I haven't tested that recently.

You could also try going to the Remote Server Info tool and looking at Active Queries and Open Files to try to see what is holding the lock on the primary key. I wouldn't expect that an open transaction on a primary key would block you from inserting a new foreign key, unless the primary key field itself was being modified. In general that's something to be avoided.

[former_member604454]

I'm quite sure it is not actually in a transaction. This:

EXECUTE PROCEDURE sp_mgGetAllLocks('C:\TTW\Data\TWOrder.adt');

does not return the order in question. Intermittently other records, but never the one I am working with. The error started yesterday and persists today, even though I restarted the service last night.

[michael_loop]

I'm not sure why the first part of the error would say that the key does not exist, and then the second part would say that the key is in a transaction. Maybe the primary key itself was being modified?

Restarting the server should clear any transactions. If not, you could try deleting any .tps files in the Transaction Log Files path (C:\ by default).

[former_member604454]

I will need to delete those while it is stopped, correct? I will try that tonight.

[michael_loop]

Error 7076 means, "No Primary key was found for the foreign key". Why would you expect this to change after a server restart?

I can't find any error with the text "Referenced field is in a transaction".

Can you give specifics about why you think the referential integrity error is inappropriate?

[former_member604454]

I should have included the full text in the body of my question. I'm sorry. As referenced in the title, the error is because of a transaction. The key exists.

"Error 7076: A corresponding primary key value was not found for the foreign key value. The primary key found in TWOrder:FILENO is currently involved in a transaction by another user. Update cannot be performed."

I would expect the restart to make sure the transaction is cleared.