I have a situation where records are mysteriously disappearing from a table. 
How can I monitor the table to discover who or what is accessing it or
executing statements or stored procedures on it?  I have tried SQL Profiler
but don't know how to filter properly.

j,

You probably should spend more time to learn Profiler. You could put a
delete trigger on the table and write to an audit table - this is more
complex than learning Profiler.

-- Bill

Show quoteHide quote

Hello,

Follow the steps:-

1. Open Profiler and trace the events

    "RPC: SP:Completed "
   "SP:Completed "
2. Run this events for the doubtful time

3. Stop and Save the profiler trace output into a table

4. Query trace table based on TEXTDATA column

Thanks
Hari



Show quoteHide quote

jmelkerson (jmelker***@discussions.microsoft.com) writes:
First of all, which version of SQL Server?

A good trace would be one that includs the events TSQL:StmtCompleted and
SP:StmtCompleted with a filter on textdata that includes the table
name, for instance '%DELETE%mytable%'.

While you can use Profiler to set up the trace, it's better to save the
trace as a script, and then run the trace as a server-side trace to a file.
This takes less resources. You can then use fn_trace_gettable to read the
file.

I don't know you think that you are missing rows, but sometimes the cause
for missing rows is that the transaction that inserts the rows is rollbacked
unexpectedly. This you cannot see in a trace. Then again, if you run a
trace for 24 hours, and you miss rows at this point but the trace is empty,
this is a strong indication of a rollback problem.

SQL Server Management Studio - long names in tabs
Help with SQL 2005 and Sourcesafe 6
SSIS and polling
Little Lock Symbols
xml viewer