May 16, 2014 at 8:48 am
I have a check constraint for a table that's part of a partitioned view. It has been working fine for several years. But lately the "Check Existing Data on Creation or Re-Enabling" will be reset to "No" at certain time of the day for no reason. I certainly don't have a job that makes any change to it.
I noticed that if I query sys.objects, this constraint would have a different modify date than the create date after this mysterious change. But the modify and create dates would be the same if I manually went in through Management Studio UI and reverted the "No" to "Yes".
My question is, what would have caused the constraint being modified? How do I go about finding the culprit? There are 7 tables in this view and this is the only one that got flipped. Is it possible that a file backup job would do this? My company uses a certain online file backup system besides regular sql server backup on the sql server.
May 18, 2014 at 5:32 am
Anything in the default trace? If the 'modify date' in sys.objects is accurate you could use that as a start to search the default trace.
May 18, 2014 at 7:22 am
Constraints (or other database objects) don't change on their own. Someone has to run DDL either through SSMS or SQLCMD, or another third party tool that can modify database objects. I agree that you should look at the default trace and if that doesn't help set up your own server side trace to capture those changes when they occur.
May 19, 2014 at 9:27 am
Thanks, guys. Very good point to use the default trace. Will try that. I remedied the case by reinforcing the constraint check in the stored proc every time before it does the partitioned view update. Doesn't solve the root problem, but it does the trick.
May 19, 2014 at 9:38 am
Is is possible that an SSIS package or some other ETL process can be disabling the check constraints prior to loading tables?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 7, 2014 at 3:16 pm
As it turned out, the check constraint was modified by a job that did bulk insert. I didn't realize that default behavior for bulk insert turns off check constraint, and the job rarely kicked off, hence the mystery. Once I explicitly checked constraint with bulk insert, the problem went away.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply