How, to track what deletes data

Question

How, to track what deletes data

Krasavita

SSC-Insane

Points: 23138
More actions
July 24, 2009 at 7:37 pm

#134608

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

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

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

ps. One Orange Chip Points: 29422 More actions · Answer 4

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

Pradeep Singh

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

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?

Lowell SSC Guru Points: 323518 More actions · Answer 6

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!

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

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

I created sp under master

Krasavita SSC-Insane Points: 23138 More actions · Answer 8

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

Thank you

Lowell SSC Guru Points: 323518 More actions · Answer 9

--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!

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

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

Lowell SSC Guru Points: 323518 More actions · Answer 11

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!

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 12

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