SQL Server 2005: Audit random record deletion.

Question

This may seem like a dumb question, but I'm in a head-> wall situation right now.

I work on a massive ERP application in which the SQL Server 2005 database is updated by multiple disparate applications. I'm trying to figure out where the deletes in a particular table are originating from.

I tried using the Profiler but I'm not able to filter the event types enough to be able to identify the errant SP because there are so many hits to the database every second from various quarters. Also the Profiler seems more directed to finding DDL changes or Object DROP type actions.

I'm simply trying to answer the question: What Stored Proc. or SQL query caused a record to be deleted from Table X?

What tool should I use? I was hoping to avoid something like Trigger based Auditing. Or is the Profiler the best tool for this sort of investigation? Or are third-party tools the only resort?

Please provide any helpful links you can because I'm relatively unfamiliar with this topic. Thanks for any help!

Answer 1

You could use SQL Profiler for this, but you need to filter the results. To monitor DELETE statements select "RPC:Starting" and "SP:Starting" events and apply a filter on the TextData column: "TextData LIKE '%DELETE%FROM%'".

-Edoode

Answer 2

Finding the culprit with profiler could be like finding a needle in a haystack, especially on a busy system; if you can't find it with filters like edosoft suggests, try to minimize the noise by eliminating statments with writes=0, filter by application name, filter by textdata not like '%select%'; you should be able to get it narrowed down.

If you're really desperate, you could deny delete permission to all users on the table and wait for the phone to ring.

You could also run occassional SELECT COUNT(*) on the table into a work table with timestamps and try to correlate any drops in record counts to other activity.