Extended Events to trace Foreign Keys becoming untrusted

Iain Barnetson 106 Reputation points
2020-08-21T15:28:25.193+00:00

I am trying to find out what is continually setting the Foreign Keys and Constraints to untrusted. I've setup an Extended Events session to trap Alter Table events but it's not catching it. How can I trap this using Extended Events?
WHERE ([sqlserver].like_i_sql_unicode_string

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,998 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,236 Reputation points
    2020-08-23T03:55:29.533+00:00

    A likely cause of untrusted constraints is bulk insert operations without the CHECK_CONSTRAINTS option. Capture the bulk_insert_usage, databases_bulk_copy_rows, and databases_bulk_insert_rows events along with the statement and other information needed to identify the culprit.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 112.7K Reputation points MVP
    2020-08-21T21:55:57.993+00:00

    I would rather use a DDL trigger to capture this. I can't think of any command but ALTER TABLE that can cause an FK constraint to be untrusted, but I have a feeling that there can be one. So I would start with capturing all DDL events and just insert the output of eventdata() to a table. If there is a lot of DDL activity in the database, you may have to refine it.

    0 comments
  3. Cris Zhan-MSFT 6,636 Reputation points
    2020-08-27T07:28:46.713+00:00

    Hi,

    Untrusted constraints can be found when you alter/drop foreign key relationships and then add them back without the proper syntax.

    https://www.brentozar.com/blitz/foreign-key-trusted/

    https://wateroxconsulting.com/archives/untrusted-foreign-keys/

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.