October 4, 2007 at 1:07 pm
Hi everyone,
We have a table in SQL 2K5 that mysteriosuly gets data deleted from every so often.
I wanted to capture the culprit and have setup a trace using profiler with Filter set on the Database Name, Object ID and Object Name. But when running, Profiler captures all transactions occurring on the server, which don't reference that table I'm trying to audit.
Is there a known problem with the SQL2K5 profiler as far as filters go?
I have SP2 installed as well.
Thanks
Narine
October 4, 2007 at 4:04 pm
Instead of "Database Name" use "Database ID"
* Noel
October 4, 2007 at 4:44 pm
Thansk Noel,
I've tried all filters by now, Database ID/Name, Object ID/Name, Text Data.
Nothing seems to work.
Not sure what else to try.
October 5, 2007 at 11:24 am
What happens is that if the delete statement is sent in a BATCH objectID is not available for batches just for SP:Statements.
So My advice is:
You use and INSTEAD OF trigger and capture the Information from sys.dm_exec_requests and sys.dm_exec_sessions
-Noel
* Noel
October 8, 2007 at 12:00 pm
Save your trace into a table and filter it there. I dont trust SQl profiler filters at all.
Or filter it in SQL Trace Analyzer (google it)
October 8, 2007 at 9:30 pm
A database or server trigger would be a good solution for this one, rather than looking for the culprit why not simply prevent the act in the first place?
October 8, 2007 at 9:53 pm
I thought the Profiler bugs were fixed in 2005 regarding objects.
A trigger, however, is a good way to try and capture what's going on. At least audit who/when this happens.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply