How, to track what deletes data

  • Some how our data periodically gets deleted in the tables. We don't know why?How can I find out, what procedure does it?

    Thank you

  • Krasavita (7/24/2009)


    Some how our data periodically gets deleted in the tables. We don't know why?How can I find out, what procedure does it?

    Thank you

    Trace is the answer. However it's not advisable to run trace always as it does use a good amount of resources.

    You can write delete triggers on the affected tables and keep the information in an audit table whenever some delete happens on those tables.



    Pradeep Singh

  • running profiler to create a trace is resource intensive, but a server side trace has minimal impact, and can help track down your deletes. search for trace on this site, there's lots of good examples here.

    If you add a DML trace , you can query every insert/update/delete statement, and figure out where the deletes are coming from.

    there's a thread here with my example of the trace i use:

    http://www.sqlservercentral.com/Forums/FindPost745574.aspx

    if the deletes seem to be "regular" when you say periodically, did you review all your scheduled jobs to see if maybe that might be doing the deleting?

    one kewl idea i saw in another thread was to create a simple table with a foreign key to the table getting deleted...insert every current id in the parent table, and wait for the constraint to prevent the next batch o deletes.

    that stops the deletes and also raises an error im whatever was doing the deleting, that error might help find the application or job that not have been obvious otherwise.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • There's a default trace as well. Search that, it might have the info you need if you check it soon after the event.

  • I think default trace doesn't track DML operations... 😉



    Pradeep Singh

  • Thank you, My question is under which db I need to create a sp_AddMyTrace and then run it. I need to investigate for all db how data gets deleted. Under this instanse SQL Server Agent is disables, so I can't see any jobs. Do I need to create sp_AddMyTrace for all db?

  • just once. run it just once.

    sp_AddMyTrace creates a log for all statements in all databases.

    it is limited to 100 meg of data before it starts rolling over to reuse the space for the logs.

    i suggest running it in master, because it creates a view named sp_DMLTrace

    if it exists in master, even if you happen to be in the PRODUCTION database, if you do SELECT * FROM sp_DMLTrace, you get results because of the whole "object starting with sp_ is assummed to exist in master"

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I can't find a view. I get this Invalid object name 'sp_DMLTrace'.

    I created sp under master

  • How to see if trace running, and how stop and delete trace?

    Thank you

  • --confirm the view exists so you can select from it:

    SELECT * FROM master.dbo.sysobjects where xtype='V' and name like 'sp_%'

    --no results means it's not in master, is it somewhere else? check ALL databases

    EXEC sp_msForEachDB 'SELECT ''?'' As DbName, * FROM ?.dbo.sysobjects where xtype=''V'' and name like ''sp_%'' '

    --see all your traces:

    select * from sys.traces

    --stop the trace( we are assuming the traceId is 2)

    declare @traceid int

    set @traceid=2

    --stop the trace

    exec sp_trace_setstatus @traceid, 0

    --start the trace again

    exec sp_trace_setstatus @traceid, 1

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It looks that I have 2 traces:

    11C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\LOG\log_6.trc20NULL50101212812451840NULL2009-07-08 15:07:24.1732009-07-26 17:12:56.36742918NULL

    21C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\LOG\MyDMLtrace.trc50NULL001002128262144NULL2009-07-26 17:11:51.1502009-07-26 17:12:56.383142NULL

    It is in master db:

    sp_DMLTrace615673241V 1000002009-07-26 17:23:13.860000V 0202009-07-26 17:23:13.8600000000

    In view only shows thing that I did and nothing how data gets deleted

  • the trace shows everything that happens from the moment you created it; it can't show what happened in the past, but if anything deletes again, you now have the tools to track what happens from here on out.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Just an alternative not yet mentioned, we have a 3rd party tool - APEX SQLLog reader. It will read your active or backups of your transaction log and tell you every detail of every transaction.

    Tim White

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

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