Re: Activity Monitoring for a Single Table
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 6 Dec 2007 22:22:08 +0000 (UTC)
William (Bill) Corder (wscorder@xxxxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Activity Monitoring for a Single Table
- From: William \(Bill\) Corder
- Activity Monitoring for a Single Table
- Prev by Date: Re: Activity Monitoring for a Single Table
- Next by Date: Re: Activity Monitoring for a Single Table
- Previous by thread: Re: Activity Monitoring for a Single Table
- Next by thread: Management studio crash if Visual Studio is running and vice verce
- Index(es):
Relevant Pages
|
