Delete or truncate

Question

Delete or truncate

ramana3327

SSCoach

Points: 19722
More actions
April 13, 2015 at 10:50 pm

#317396

Hi,
I am working in testing environment.
Some one deleted all data from all most all tables in a particular database. I want to find when that happened and which login did? I don't need to recover that data for now but need to know when the truncate of tables happened?

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

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

ramana3327 (4/13/2015)
Hi,
I am working in testing environment.
Some one deleted all data from all most all tables in a particular database. I want to find when that happened and which login did? I don't need to recover that data for now but need to know when the truncate of tables happened?

Without auditing in place this is likely to be more of a forensic exercise and the chances of success depend on quite few factors such as recovery model, has the server been restarted since etc.

😎

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

Unless you had a trigger on the table or some form of auditing in place, it is extremely unlikely that you will be able to tell after the fact who did what. If you need to track that kind of thing, you need some form of auditing in before things happen

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

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

You could try searching sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text to find DELETE/TRUNCATE statements. It will tell you when the last execution time was, if the query is still in cache. But it won't tell you who ran the query or what parameters they used. It might narrow things down a little.

"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

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

Grant Fritchey (4/14/2015)
You could try searching sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text to find DELETE/TRUNCATE statements.

Truncate doesn't get cached iirc, because it's a DDL statement

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

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

GilaMonster (4/14/2015)
Grant Fritchey (4/14/2015)
You could try searching sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text to find DELETE/TRUNCATE statements.
Truncate doesn't get cached iirc, because it's a DDL statement

Ah, true. Hadn't thought that through. Just trying to come up with something to help. There really is no replacement for setting up auditing.

"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

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 6

Since TRUNCATE is a DDL statement, might it be in the default trace? Haven't used the default trace in a million years so can't recall off the top of my head.

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

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 7

It's not in the system_health extended events session. I know that.

"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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

try reading the log to see if there's any detail in there

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Jeff Moden (4/14/2015)
Since TRUNCATE is a DDL statement, might it be in the default trace? Haven't used the default trace in a million years so can't recall off the top of my head.

I don't think it is. The default trace tracks Object:Create, Object:Alter and Object:Drop, and truncate is none of those.

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 10

Perry Whittle (4/15/2015)
try reading the log to see if there's any detail in there

This is probably your best option without any auditing in place. Of course, you'll need those log records to exist, either in the current log or in a backup (so if the DB is in Simple recovery this option is also out).

Paul Randal has a nice writeup about doing something like this: http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

Even when that's feasible it can be a bit tedious, though. I'll pile on and echo that there's really no substitute for proper auditing.

Hope this helps!