2

I have a delicate situation wherein some records in my database are inexplicably missing. Each record has a sequential number, and the number sequence skips over entire blocks. My server program also keeps a log file of all the transactions received and posted to the database, and those missing records do appear in the log, but not in the database. The gaps of missing records coincide precisely with the dates and times of the records that show in the log.

The project, still currently under development, consists of a server program (written by me in Visual Basic 2010) running on a development computer in my office. The system retrieves data from our field personnel via their iPhones (running a specialized app also developed by me). The database is located on another server in our server room.

No one but me has access to my development server, which holds the log files, but there is one other person who has full access to the server that hosts the database: our head IT guy, who has complained that he believes he should have been the developer on this project.

It's very difficult for me to believe he would sabotage my data, but so far there is no other explanation that I can see.

Anyway, enough of my whining. What I need to know is, is there a way to determine who has done what to my database?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Bill Norman
  • 883
  • 11
  • 29

4 Answers4

4

If you are using identity for your "sequential number", and your insert statement errors out the identity value will still be incremented even though no record has been inserted. Just another possible cause for this issue outside of "tampering".

BlakeH
  • 3,354
  • 2
  • 21
  • 31
  • @bill-norman - For example, say during testing you start a transaction, perform several INSERTs and perhaps other operations, then issue a rollback to avoid changing the database. Those INSERTs will have a visible side-effect of consuming identity values. – HABO Dec 26 '13 at 17:55
  • Thanks for that information. I have error trapping in the SQL commands which write the errors to an error log, and I even have some of the more important ones send me an email when they occur. I never received any emails, and the error logs have no entries that correspond to the missing entries. and based of your comment, there are no unpopulated records. Thanks again – Bill Norman Dec 26 '13 at 17:57
  • I just now understood what you are telling me. That is a distinct possibility. I'll have to take another approach to the problem. Thanks. – Bill Norman Dec 26 '13 at 20:27
  • I have seen this problem on more than one occasion, so I tend to try to rule this out first – BlakeH Dec 26 '13 at 20:39
  • I marked this as the answer because it probably saved me a tremendous hassle. This raises enough doubt about my original concern that it would be imprudent to pursue that angle. Without having known that characteristic of SQL, I could have made a complete ass of myself. I really appreciate the information. – Bill Norman Dec 27 '13 at 15:14
1

Look at the transaction log if it hasn't been truncated yet:

Community
  • 1
  • 1
T McKeown
  • 12,971
  • 1
  • 25
  • 32
  • SO Link: http://stackoverflow.com/questions/4507509/how-to-view-transaction-logs-in-sql-server-2008 – Mike Dec 26 '13 at 17:32
  • I had discovered this prior to writing my post, and all it showed was three lines that are pretty much indecipherable, and containing a whole bunch of "null" cells. – Bill Norman Dec 26 '13 at 18:06
1

If you want to catch the changes in real time, I suggest you consider using SqlDependency. This way, when data changes, you will be alerted immediately and can check which user is using the database at the very moment (this could also be done using code).

You can use this code sample.

Coming to think about it, you can establish the same effect using a trigger and writing ti a table active users. Of course, if you are suspecting someone is tempering with data, using SqlDependency might be a better way to go with, as the data will be stored outside of the tampered database.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Avi Turner
  • 10,234
  • 7
  • 48
  • 75
  • Is the SQLDependency functionality a backward-looking feature? Or does it only start once it's implemented? In other words, will it show me any transactions that have occurred in the past? – Bill Norman Dec 26 '13 at 18:18
  • @BillNorman It is not a backward-looking feature. You can think of it as a trigger being triggered externally to your database. – Avi Turner Dec 26 '13 at 18:20
1

You can run a trace, for example a distant profiler trace, that will get all SQL queries containing the DELETE keyword. This way, nobody will be aware that queries are traced. You can also query the default trace regularly to get the last DELETE commands: Maintaining SQL Server default trace historical events for analysis and reporting

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
rudi bruchez
  • 624
  • 3
  • 10
  • I don't know if this is what you had in mind: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/29/learn-who-started-that-trace-with-the-default-trace.aspx - but I ran it and came up with nothing that showed anything done by that guy. But I'm not sure if this is what this is for. – Bill Norman Dec 26 '13 at 18:35
  • 1
    The default trace will tell you what happened recently, and keeping a running profiler session will tell you what will happen in the future. As you said that your project is under development, so that will allow you to know if data will be deleted in the future, and it has the advantage of leaving almost no trace of the server: if someone is really deleting data, he will not know that a trace is runnning (he could know, but usually people don't check for that, and even if he does, he won't know for sure that the goal of the trace is to trace deletions) – rudi bruchez Dec 26 '13 at 18:49