Tips for tracking down mystery update SQL?

I just started a new job and was given a bug to track down and fix. The basic issues it that a field in a DB record is being cleared out and no one knows why. So far I have tried:

• Checking the table for triggers, there are none.
• Monitoring the table using SQL Server profiler for the last couple of days in the hopes that the error would happen again but unfortunately it hasn't.
• Reviewing all the code that does inserts/updates and I didn't se开发者_运维知识库e anything that would cause this problem.

Does anyone have any other suggests for finding what could have updated this record? Am I not checking something that I should be? Are there any other sources of information that I should look at?

---

Create a trigger that will write to a history table. Include columns for the date of the write as well as the user.

---

::fn_dblog() will show you at the very least when the update occurred (as sequence, not as time) and what other operations were done by that transaction. From the information on what other operations this transaction did, along with what other transactions were doing at that moment, you should be able to narrow down at least the context under which the update occurred, from which point code inspection is a viable option to continue.

Reading the log requires ... the log so your database should be in full recovery mode.

---

select
schema_name = s.name,
object_name = o.name
from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
    join sys.schemas s on o.schema_id = s.schema_id
where definition like '%FieldName%'

This query looks through all the objects in the database (stored procedures, functions, views) and looks for any place where 'FieldName' is being refered. I would go through all the objects returned by this query to see if anything unusual is being done with the Field. This might be extremely tedious as it might return more results than you care about, but its a sure shot way of catching all the references to the field

---

If you are on SQL Server 2008 you can use extended events to get the full stack trace for the offending statement. Example code here Create Trigger to log SQL that affected table?