Do you maintain an application with a backend database? Does that application ever write to the database? Be afraid.
The worst situation to be in with respect to the above is when a customer mails you and says: “Why is my data gone?”
99% of the time, it’s because they clicked on the “Delete” button; but sometimes you need proof – this is what I want to discuss.
Log every interesting write to the database. There are various levels at which you can do this:
As you can see, the lower you go, the less chance for error and the less value a log entry gives you. You can increase the value of a “deep” log entry by forcing a set of arguments up the stack. For example, you can force every sproc which writes data to require username/machine/reason arguments – but this can become costly to maintain.
So which is right? Depends on your app, your level of paranoia, your customer’s needs, etc. Generally I stay away from triggers and try to add logging at the sproc level for critical sprocs, and at the app/OM level for extra context; better to have too much logging than too little.
Here’s a nice trick for creating a footprint log table that your sprocs (or triggers) can use quite easily - Create a simple table with the following columns (at least):
Inserting into this table requires only the one column, but you capture lots of extra data for free. If this is done from a trigger, then you’ll even catch slackers with access to the DB who are updating it manually.
Oh, don’t forget to age off old entries before the table ends up at 50GB ;)