Post reply

DML info for a table

  • February 11, 2011 at 2:34 am

    #142299

    Hi, I want to find when a large amount of data was deleted from my table and by who. Is there a way to find the change in size of tables in my database? I apprieciate your help on this. thanks.

    Thanks,
    sqlstart

  • February 11, 2011 at 3:54 am

    #1285592

    Please follow this very well written article:-

    http://feodorgeorgiev.com/blog/2011/01/what-objects-were-created-altered-and-deleted-in-the-past-24-hours/

    Obviously, as mentioned in post as well, it will work only if your default trace is enabled which you can check by running :-

    select * from sys.configurations

    where configuration_id = 1568

    if value_in_use = 1 then its enabled.

    ----------
    Ashish

  • February 11, 2011 at 5:22 am

    #1285622

    But, the default trace doesn't track data changes. If you don't already have something auditing your database, a third party tool, or a server-side trace, there's no way to find out after the fact who made changes to data in the system.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 11, 2011 at 5:41 am

    #1285625

    Thank you for the help!

    hmm, i understand that i can't see the data changes happened. But the query is really help full to track who fired the deletes on the DB, thanks to u Ashish.

    Thanks,
    sqlstart

  • February 11, 2011 at 5:45 am

    #1285626

    crazy4sql (2/11/2011)

    Please follow this very well written article:-

    http://feodorgeorgiev.com/blog/2011/01/what-objects-were-created-altered-and-deleted-in-the-past-24-hours/%5B/quote%5D

    Please stop recommending that people look in the default trace for data changes. Data changes are not tracked by the default trace in any version of SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 11, 2011 at 6:02 am

    #1285631

    @ gail..

    Then wats the alternative?

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • February 11, 2011 at 6:06 am

    #1285632

    SKYBVI (2/11/2011)

    @ gail..

    Then wats the alternative?

    Regards,

    Sushant

    There is no alternative out of the box. If you need to track data changes you must create mechanisms for doing that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 11, 2011 at 6:10 am

    #1285635

    You mean a trigger??

    But, wats wrong in seeign hte default trace for data changes, i cant get tht.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • February 11, 2011 at 6:22 am

    #1285641

    Please stop recommending that people look in the default trace for data changes. Data changes are not tracked by the default trace in any version of SQL.

    then what is the post about?

    ----------
    Ashish

  • February 11, 2011 at 6:38 am

    #1285649

    SKYBVI (2/11/2011)

    You mean a trigger??

    But, wats wrong in seeign hte default trace for data changes, i cant get tht.

    Regards,

    Sushant

    What's wrong with using the default trace to look at data changes is that there are no data changes recorded within the default trace. They're not there. You can't use it to do something that it is absolutely incapable of doing.

    Now, you can set up a server side trace to capture stored procedure calls and query's. That's easy and encouraged (just be sure you're prepared to deal with the data involved), but that's different than the default trace.

    Yes, you can use triggers, Change Data Capture in 2008, third part tools, multiple statements in queries, the OUTPUT clause... There are a lot of ways to set up auditing. But you have to set it up. There is nothing running and waiting for you in SQL Server that does data auditing.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 11, 2011 at 7:10 am

    #1285675

    crazy4sql (2/11/2011)

    Please stop recommending that people look in the default trace for data changes. Data changes are not tracked by the default trace in any version of SQL.

    then what is the post about?

    What is what post about?

    The OP asked how to track deletions and you answered with feature that only tracks schema changes (create, alter, drop), not DML (select, update, insert, delete_

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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