Post reply

Profiler. How to audit updates only on one table not on entire DB

  • November 9, 2006 at 6:55 am

    #62374

    I use profiler. It is a powerfull tool, but I cann't find what I want. Tried to make the filter regarding to ObjectID, but it seems it doesn't work- I get a huge list of various records through the entire DB(filtering Database ID works, but that's not enough for me). The thing is that I need to identify all SP which updates one particular column in the x table. Is it possible?

    Thanks

  • November 9, 2006 at 7:59 am

    #670843

    You can use an UPDATE trigger, and send the update event to your SQL errorlog.

  • November 9, 2006 at 12:09 pm

    #670908

    I couldn't agree with you. Trigger could be the best solution but how to get the procedure name, that updated the records? That appears the main problem  to me  for now

  • November 9, 2006 at 2:15 pm

    #670937

    How about use the filter in the SQL Profiler?

  • November 10, 2006 at 2:30 am

    #671026

    1) Run the SQL profiler ( choose template or create one yourself )

    2) save the tracle to a file

    3) Open the trace you just saved and filter the trace

    for a more permanent logging

    1) use a trigger on the X table and log it into a reporting table

    2) alter the scripts which are actual updating the X table and log the reporting table from there

    3) DO NOT forget to clean up the reporting table every now and then ( keep a history for 90 days ) 

    Regards,

    GKramer

    The Netherlands

  • November 10, 2006 at 5:15 am

    #671043

    Ok, from yours suggestions I see that there are two possible ways to accomplish my goal:

    1. Trigger

    2. Profiler

    I've tried these both ways, but the drawback of the first is that I couldn't find the way to show me the procedure name which updated the record. Do you know how to do that?

    Talking about Profiler, it generates a lot  of results from entire Database. I choosed Event: Stored ProceduresP:SmtCompleted(as I remember), and the column Textdata shows the SP syntax. From there I can filter the queries that updated my x table. But I think this is not the best way.

    Does anyone know, what events, columns and filters I should take into my x table audit trace?

    Thanks a lot

  • November 10, 2006 at 5:23 am

    #671046

    there's actually a different approach if your app is all sp driven and you just want to know which procs access your table - do a search in syscomments.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 10, 2006 at 5:40 am

    #671049

    Colin,

    are you telling me that the syscomments table will tell me what procedures had updated the x table particular column?

    I have not a possibility to take a look at the yours suggested table now... But if you correctly understood me and you say the truth I would be very appreciate to you for the such info

  • November 10, 2006 at 12:48 pm

    #671168

    You can capture the SQL that caused a trigger to fire.  I forget exactly the layout for the logging table, but if you play in your sandbox, you should be able to figure it out.

     

    Create trigger foo on bar

    For Update,Insert,Delete

    as

    Insert Forensic.dbo.LoggingTableforTrigger_bar

    EXEC ('DBCC Inputbuffer (@@SPID)')

    GO

    Now, when the trigger on the table [bar] is fired, the currently-executing code (That caused the trigger to fire) gets logged in the table.

    hth jg

     

     

     

     

  • November 12, 2006 at 12:15 pm

    #671248

    Jeff,

    you are genius! Thank you very much.

    Good Luck!

     

    Vidas

  • November 13, 2006 at 6:24 am

    #671304

    Vidas,

    I should mention that putting that kind of trigger in a production environment could be dangerous, so use it with caution.

    For example, if the table gets updated by a procedure that uses EXEC ('SomeSQLStatement'), the trigger will fail due to nested EXECs.  This could have some unintended consequences.

    jg

     

  • November 13, 2006 at 7:02 am

    #671314

    Jeff,

    Thanks about the warning. The worst thing that could happen is that some nested procedure will not be noticed. I think the main goal is to identify the primary procedures, and the others(nested) will be discovered manually.

    For the present I'm satisfied with results. Thanks again

     

    Vidas

Viewing 12 posts - 1 through 11 (of 11 total)

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