Auditing a table that's being truncated

Question

Post reply

Auditing a table that's being truncated

dbv147

SSC Veteran

Points: 238
More actions
November 10, 2008 at 11:19 am

#202601

Hello,
I've just started in a new job and 1 of my 1st tasks to track down a job that is truncating a table. What I'd like to get is people’s ideas on the best way to audit this table so I can track down the culprit. Basically this table is loaded by a SSIS package. I verified that the SSIS package is not the issue. I can run it manually and it populates the table fine and does not truncate it. Any suggestions would be greatly appreciated. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Check security on the table and remove any users that may have those rights that don't absolutely need those rights.

Also you can use Profiler to trace or create a Server-side trace to audit the database.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

It's got to be someone with owner level permissions. I didn't think DELETE allowed this.

I'd run a trace, looking for that table or for the TRUNCATE command.

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 3

I would create a profiler job that captures events for that database and filter it for truncate commands. That should get you the informaiton you need.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Have you checked the creation date of the table to verify that it is not being dropped and re-created, instead of truncated?

dbv147 SSC Veteran Points: 238 More actions · Answer 5

Thanks for all the prompt replies. It took me a bit to narrow down the time frame that the table was being truncated so I could run a trace that didn't run all night.

So I've got the trace and I see the exact time the truncate is happening however I'm not seeing exactly what SP is doing the truncate and from what server it's happening. Is there any way I can get that info from the client process id or some other ID?

I believe that there is a SP on another server running the SSIS package and then doing a truncate after running the SSIS package.

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

are you running server side trace or through profiler?

what events are you capturing?

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

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

dbv147 SSC Veteran Points: 238 More actions · Answer 7

Thanks for all the suggestions everyone. I finally got what I needed. For some reason I wasnt getting the server name that was calling the SSIS until I captured the locks in the trace.