July 24, 2009 at 7:37 pm
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
July 24, 2009 at 11:17 pm
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.
July 25, 2009 at 3:40 am
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
July 25, 2009 at 10:44 am
There's a default trace as well. Search that, it might have the info you need if you check it soon after the event.
July 25, 2009 at 11:20 am
I think default trace doesn't track DML operations... 😉
July 26, 2009 at 7:47 am
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?
July 26, 2009 at 8:07 am
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
July 26, 2009 at 8:35 am
I can't find a view. I get this Invalid object name 'sp_DMLTrace'.
I created sp under master
July 26, 2009 at 11:16 am
How to see if trace running, and how stop and delete trace?
Thank you
July 26, 2009 at 11:29 am
--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
July 26, 2009 at 7:34 pm
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
July 26, 2009 at 8:11 pm
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
July 27, 2009 at 9:05 am
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