Data getting deleted automatically

Question

Data getting deleted automatically

sharonrao123

Default port

Points: 1450
More actions
July 20, 2009 at 10:52 pm

#207196

hi All,
I have two tables tableA and tableB. The data is getting deleted from these tables automatically this is not happening everyday but happens randomly.
The first time it happened i created a delete trigger on both tables to copy the deleted data into another table. Recently it happened on 17/07/2009 which is friday midnight and the data also gets replicated on the webserver which poses a big problem for me. So i need to fix this asap.
What i trying to figure out is how can i stop this from happening? Can i use the SQL server profiler to capture only the delete operation on the database?
Please get me started on this, i appreciate your time and expertise.
Thanks.

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I suggest you put a trigger for delete and capture as much information as possibile on the delete command and put the results in an auditing table.

Just a hint to get you started:

CREATE TRIGGER someTrigger

ON TableA

FOR DELETE

AS

BEGIN

DECLARE @SQLBuffer nvarchar(4000)

DECLARE @buffer TABLE (

EventType nvarchar(30),

Parameters int,

EventInfo nvarchar(4000)

)

INSERT @buffer

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

SELECT @SQLBuffer = EventInfo

FROM @buffer

--Gather some more info from DMV

--sys.dm_exec_connections

--sys.dm_exec_sessions

--sys.dm_exec_requests

INSERT INTO AuditingTable

SELECT GETDATE(), @SQLBuffer, host_name, program_name, login_name, nt_domain, nt_user_name

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID

END

Once you find the program issuing the delete query, you can go and fix it.

Hope this helps

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323506 More actions · Answer 2

i would suggest adding a trace as well as the fine trigger example above; there's a thread here with my example of the trace i use:

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

with the trace, you'd see all the commands that were issued,against every database and every table, so you can use that context to see what program/application is doing it, or whether it was a specific user9developer connecting to production, etc)

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!

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

You might also want to make that trigger an "Instead of" trigger so that not only are you capturing the information you want to catch the culprit, you're also preventing the deletes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

create something that uses it as a foreign key. It should error the delete out.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sharonrao123 Default port Points: 1450 More actions · Answer 5

hi All,

Thanks a million for your time and suggestions. I have taken the advice of Gianluca and included the t-sql code to capcure the audit information in the existing AFTER DELETE triggers on both the tables.

One of the suggestion was to create a Instead of Trigger for delete, i was wondering if i create that then dataentry people will not be able to delete any data in case they need to. Or is there a smarter way to create a INSTEAD of Trigger on delete which will alow the dataentry people the delete operation and only fire when a lot of rows are getting deleted? Please advice if i can do this.

Thanks,

Shilpa.

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 6

No don't use instead of trigger.

I would like you to go ahead and enable default trace on by using sp_configure command. now you can easily see all the logs by using following command. This command will give you the place where default file exists.

SELECT * FROM FN_TRACE_GETINFO(DEFAULT)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

luckysql.kinda (7/22/2009)
No don't use instead of trigger.
I would like you to go ahead and enable default trace on by using sp_configure command. now you can easily see all the logs by using following command. This command will give you the place where default file exists.
SELECT * FROM FN_TRACE_GETINFO(DEFAULT)

What's wrong with the instead of?

And - seeing the logs is nice (there's an actual report for that, so going to the query is frankly overkill), but making sure you still have the data and it NOT get deleted is actually a bit better than just knowing "yup - it got deleted all right".

And Sharon - you certainly could do something like that, you just have to define the break logic (meaning decide what constitutes "a lot of records"). If you issue the delete from within the instead of trigger, the delete will happen.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

vipinmittalmca SSC Enthusiast Points: 116 More actions · Answer 8

Sharon i am also facing the same problem in the same manner. The data is deleted from database but i am not able to identify the process or application which is doing this and its frequency is very much random.

I am curios to know if you were able to resolve this issue if yes then how.

Thanks in advance.
Vipin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

vipinmittalmca - Tuesday, March 7, 2017 10:11 PM
Sharon i am also facing the same problem in the same manner. The data is deleted from database but i am not able to identify the process or application which is doing this and its frequency is very much random.

Please start a new thread for this rather than bumping an 8-year old one. The person you're asking hasn't logged in for 2 years.

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

vipinmittalmca SSC Enthusiast Points: 116 More actions · Answer 10

Thanks Gail for suggestion. I will start a new thread.

Thanks
Vipin