how can we know the deleted userinformation on a particular table in DB

Question

how can we know the deleted userinformation on a particular table in DB

raghava_tg

Ten Centuries

Points: 1014
More actions
September 17, 2012 at 7:10 am

#266760

Hi ,
in last couple of days, one table is truncating means its having zero records init.
it is very useful table to our process. but it is truncating in last couple of days.
my question how can we know the information of a user who truncating the table . may be it is a sqlserver Agent.
Please let me know in case of any additional data required.
Thanks in advance.

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

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

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 1

The easiest way is to either set up a trigger to log delete actions or to set up SQL PROFILER to capture the activity on this table.

If you had a Forgein Key constarint on the table it wouldn't allow the truncate to run.

tim_harkin SSCrazy Points: 2399 More actions · Answer 2

Not sure if a delete trigger solves the problem, because TRUNCATE is non-logged. That is why it is so quick, but also why it requires elevated permissions.

Is it truly a truncate or a DELETE *?

raghava_tg Ten Centuries Points: 1014 More actions · Answer 3

raghava_tg

Ten Centuries

Points: 1014

September 17, 2012 at 7:27 am

#1538595

not sure, but it is having only emply data in it.!!!

balajisrm Right there with Babe Points: 723 More actions · Answer 4

As you say that this data in the table is getting deleted only for the last couple of days,

Kindly check the recently deployed codes (Stored Procedures, Patches) for the week if possible. If truncate is used you cannot do anything. Try to add a constraint to the table or fira trigger ON DELETE for the table. This can help you to find the culprit.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

tim_harkin (9/17/2012)
Not sure if a delete trigger solves the problem, because TRUNCATE is non-logged. That is why it is so quick, but also why it requires elevated permissions.
Is it truly a truncate or a DELETE *?

To be clear a TRUNCATE IS logged. Every action in sql is logged (ACID). A TRUNCATE logs the pages not the individual rows, that is why it so much faster.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

balajisrm (9/17/2012)
As you say that this data in the table is getting deleted only for the last couple of days,
Kindly check the recently deployed codes (Stored Procedures, Patches) for the week if possible. If truncate is used you cannot do anything. Try to add a constraint to the table or fira trigger ON DELETE for the table. This can help you to find the culprit.

All is not lost if it is a truncate. You can still use DDL triggers to capture the truncate and either prevent it or log it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

As Sean said, TRUNCATE is a logged operation. TRUNCATE, however, does not fire DELETE triggers on tables. If you need to know who is deleting data from the table, you will need to setup a server-side trace or use extended events to capture that information.

raghava_tg Ten Centuries Points: 1014 More actions · Answer 8

Thanks Lynn and to others.

I have been created to Trace file which capture the transacations performed on table by any user...

Thanks again!!!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

Since this is a 2008 forum, let's go with extended events or change data capture. Either of these will let you know who modified data. Triggers are incomplete and can be very resource intensive. Profiler should not be run against a production system. Server-side traces (scripted components of Profiler) are OK, but not as lightweight as extended events. I'd go with extended events.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning