Post reply

How to trace who run SQL command "delete" on specific dbase?

  • September 2, 2009 at 1:48 am

    #95989

    Guys, How will i know who run the SQL statement "delete" ex. DELETE * FROM tbl WHERE date='00/00/0000'

    Because someone deleted my data on my dbase, I based it to my LogID, The sequence changed so meaning some of the data was deleted.

    I tried checking SQL Logs but there is no kind of logs saying that someone run sql statement.

    Thank you in advance

    Thanks,
    Morris

  • September 2, 2009 at 2:03 am

    #1047974

    Unless you actively put procedures in place to log DML actions, then there is nothing captured.

    Sorry.


    Clear Sky SQL
    My Blog[/url]

  • September 2, 2009 at 3:18 am

    #1047992

    Hi,

    Is be very sure that the sequence of records change means some records were removed from the table?

    Try with this table output

    select * from master..syscacheobjects

    where objtype = 'ADHOC'

  • September 2, 2009 at 4:22 am

    #1048004

    Thank you very much for the reply but on this query

    select * from master..syscacheobjects

    where objtype = 'ADHOC'

    It has a field of sql but how will I know on when the sql was executed? And also there's only 90+ rows i found It's very few transactions. Is this query just show the latest event in SQL Server? Like for example 1 hour events only?

    Thanks,
    Morris

  • September 2, 2009 at 5:21 am

    #1048032

    Damien (9/2/2009)

    Thank you very much for the reply but on this query

    select * from master..syscacheobjects

    where objtype = 'ADHOC'

    It has a field of sql but how will I know on when the sql was executed? And also there's only 90+ rows i found It's very few transactions. Is this query just show the latest event in SQL Server? Like for example 1 hour events only?

    This query will show you how the cache was used not when the data was deleted. As mentioned above unless you have triggers in place there is no way to find out who deleted your data.

  • September 2, 2009 at 6:22 am

    #1048062

    steveb (9/2/2009)

    As mentioned above unless you have triggers in place there is no way to find out who deleted your data.

    Or a server-side trace.

    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 6 posts - 1 through 5 (of 5 total)

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