2

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 see 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?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486

4 Answers4

2

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

Jeffrey Jochum
  • 76
  • 2
2

::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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • How far would this log go back? – Abe Miessler Nov 13 '10 at 00:34
  • If database is in full recovery then until the most recent log backup. In simple recovery until the most recent checkpoint (few minutes). It can be further analyzed in the the log backup files themselves, by restoring the database under a different name and then applying logs one at a time and analyzing the log content before applying a new log. – Remus Rusanu Nov 13 '10 at 01:35
0
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

Sadhir
  • 413
  • 4
  • 13
0

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?

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845