CODEWITHSUNDEEP
sql-server-2008
Dustin Davis
Dustin Davis

Reputation: 14585

SQL Profiler: Capture writes for specific table(s)

I'm needing to capture write operations (any query that changes data) but only for two specific tables. Is this possible to configure in Sql Profiler? If so, how? I'm hoping for a solution that doesn't involve filtering on the text.

Upvotes: 4

Views: 6379

Answers (3)

Dustin Davis
Dustin Davis

Reputation: 14585

Based on comments on the other's answers, use SQL Profiler as you want to with the following setup

Event Selection:
-Stored Procedures
  - SP: StmtStarting
-TSQL
  - SQL:StmtStarting

Column Filters
-TextData (LIKE)
 - "insert %"
 - "update %"
 - "delete %"

I didn't want to filter on the text data, but that's the only way to get what I want.

Upvotes: 8

RC_Cleland
RC_Cleland

Reputation: 2304

Use change data capture which is explained in detail here

If CDC is not your selection consider placing a trigger on the table(s). Within the trigger capture data from SQL Server built in functions such as 

select App_name() as 'AppName', CURRENT_USER as 'CurrentUser', HOST_ID () as 'HostID', HOST_NAME () as 'HostName', SESSION_USER as 'SessionUser', SYSTEM_USER as 'SystemUser'

Then place the data into a history table

Upvotes: 0

RThomas
RThomas

Reputation: 10880

I agree with other answer that CDC is the way to go (as long as you're on Enterprise edition)

Using profiler it is technically possible, but only barely.

You'd start by capturing TSQL: StmtStarting, TSQL: StmtCompleting

You'd then have to export the list for further analysis. You'd have to write some type of analyzer to filter out statements that didn't meet criteria. If your system gets ad-hoc queries or is laced to a system that generates a large variety of queries, analyzing the list would quickly get exponetially hairy.

There is also the issue that running profiler has significant overhead and if you wanted to get these numbers over a long period of time you'd quickly stress system resources into unacceptable levels for most production systems.

In short, it might kind of be fun to try and use profiler in this way, but not in a "real world" scenario.

And this coming from a guy who enjoys watching lawn mower racing.

Upvotes: 0

Related Questions