Audit Trails and Logging Part I

  • I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?

    I was referring to the SQL Server trace files created by sp_trace_create, sp_trace_setevent, etc which is the method SQL Profiler uses.

    David

  • I've been using trigger-based Audit Trails since SQL6, and every customer I've set them up for has loved them. Although there have been some use cases for workflow reporting and undo, the primary purpose has been tracking who-changed-what-when. Although you have classified this as Blamethrowing, it actually serves many other purposes. For instance, a user asks why their scheduled search did not return an expected result when expected, and I can show them that a) they added a criteria after the matching event had happened, and b) how to figure that out without asking me next time. It also answers questions about how a record got into a certain strange state, when there can be many users using multiple applications... and it can thereby turn up cases that need to be handled better in code.

    I agree that the audit trail tables can be tampered with by anyone with permission, but so far I haven't had to satisfy a legal requirement... if I did, I would just keep the log files as a backup in case there was any question.

    Also, although there seems to be a consensus that triggers need to be hard-coded to the table structure for speed, and thus rebuilt when the schema changes, I have worked out a way to quickly loop through existing columns, and only save changes, eliminating updates to the same value. I will be interested to see where your next article stands on this.

  • Hi,

    I just want to point you to a free tool for monitoring the SQL Log files.

    It is Log Parser currently in v2.2 form Microsoft.

    It is using a query engine and can read all kinds of log files and event logs.

    It can output to the SQL database so you can build your own reporting on top.

    SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).

    It would be interesting if you investigate the tool against those $1000 bucks tools.

    Florin

  • Good tip! Thanks!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • fmuntean (6/9/2008)


    Hi,

    I just want to point you to a free tool for monitoring the SQL Log files.

    It is Log Parser currently in v2.2 form Microsoft.

    It is using a query engine and can read all kinds of log files and event logs.

    It can output to the SQL database so you can build your own reporting on top.

    SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).

    It would be interesting if you investigate the tool against those $1000 bucks tools.

    Florin

    I was actually looking at that product a little while ago. From what I could tell, it could parse the event file, error log, etc., but I can't tell from the web page whether it will read an SQL Server transaction log. Have you tried that? Does it work well for it?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Good one Gus, well presented. Looking forward to reading the second article.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply