Activity Monitoring for a Single Table

We started experiencing a strange problem with one of our database last week. After analyzing the data and comparing with multiple backup sets, we found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete, change, etc? We want to do this for a day or so to identify the offending process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
using Profiler (or better yet, a server-side trace without the GUI)...
"William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
We started experiencing a strange problem with one of our database last
week. After analyzing the data and comparing with multiple backup sets, we
found that data was randomly being deleted from a single table.
Is there any way to monitor a single table for activity, add, delete,
change, etc? We want to do this for a day or so to identify the offending
process.
Thanks,
William (Bill) Corder
wscorder@.hotmail.com
|||Aaron,
Thanks, I am setting up a trace to monitor the activity.
Bill
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e5TEw8BOIHA.3852@.TK2MSFTNGP06.phx.gbl...
> Sure, look up CREATE TRIGGER in Books Online. You can also run a trace
> using Profiler (or better yet, a server-side trace without the GUI)...
>
> "William (Bill) Corder" <wscorder@.hotmail.com> wrote in message
> news:07955481-D3E1-48A9-AB7A-16ACA438B7DA@.microsoft.com...
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the offending
> process.
> --
> Thanks,
> William (Bill) Corder
> wscorder@.hotmail.com
>
>
|||William (Bill) Corder (wscorder@.hotmail.com) writes:
> We started experiencing a strange problem with one of our database last
> week. After analyzing the data and comparing with multiple backup sets,
> we found that data was randomly being deleted from a single table.
> Is there any way to monitor a single table for activity, add, delete,
> change, etc? We want to do this for a day or so to identify the
> offending process.
A trigger that logs the deleted rows together with columns populated with
the output from host_name(), app_name(), SYSTEM_USER, getdate() etc is
definitely what you need.
A server-side trace as Aaron suggested gives you the offending statement
more directly, so that is also a good idea. The log table gives you what
was deleted and when, and from that you can look up the trace.
I had to do this recently. Except that I did not come very far. That is,
before I deployed my trigger to production, I tested it, and when I ran
a delete operation from the GUI, I found that the log table said that
two rows had been deleted. Oh-oh. The culprit was a DELETE statement in
a procedure where I had forgotten the WHERE clause, blush!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The just re-did this with a trigger and it is more direct. I get exactly
what I need. All I had to do was create a separate log table to to hold the
data.
Thanks for the help and I will take a look at LogManager.

Thanks,
William (Bill) Corder
wscorder@.hotmail.com
"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam_@.upscene.com> wrote in
message news:#fQucLOOIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
>
> You probably won't get the old/new field values with that. This is easily
> done with triggers, as Aaron has pointed out.
> There are tools out there, which help you to setup a trigger-based
> auditing solution. For instance, our MSSQL LogManager product is one of
> them.
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> http://www.upscene.com