Post reply

How to track updates to a table

  • March 2, 2009 at 11:09 am

    #129356

    I need to track every update to a specific table, how do I do that.

    The problem is that a script, trigger, something is updating every row in a certain table with the wrong data. I'm not that familiar with Profiler to know how to set that up(or if it can be set up at the table level)

    Changinagain

  • March 2, 2009 at 11:17 am

    #952000

    I would recommend you use Profiler.

    I would also recommend you go to Books Online and read up on using Profiler.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 2, 2009 at 11:34 am

    #952008

    I would also recommend Profiler and reading up on how to use it. It's an invaluable tool to have in your arsenal.

    Beyond that you may want to check out the following link. It discusses how to setup an audit table and a trigger to audit the table. Depending on how active this table is, you may see a substantial performance impact, be aware of this. Try it out in a test environment first of course.

    http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 2, 2009 at 11:44 am

    #952015

    I'd go with auditing. Trace/Profiler could miss something if it gets stopped, or isn't started when the server reboots

  • March 2, 2009 at 11:51 am

    #952020

    If the database is in full recovery mode, the easiest way to find out exactly what's going on is to use a log parser on the transaction log. ApexSQL and Lumigent both offer log parsing apps.

    The advantage to parsing the transaction log is you don't have to wait for it to happen again, you can look at stuff that's already been done.

    Second best is run a trace. Lowest impact way to track down what's going on. Either Profiler, or a server-side trace, can do what you need.

    Third best is audit triggers. I wrote a couple of articles about those for this site, plus there's a ton of other material on them out there.

    - 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

  • March 2, 2009 at 2:48 pm

    #952133

    Thanks, I'll start looking at BOL and go from there

    Changinagain

Viewing 6 posts - 1 through 5 (of 5 total)

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