May 23, 2016 at 12:02 pm
Good morning Experts,
I have a server named SRVR1. I have a table t1 in database db1 and I have a table t2 in database db2. I want to setup a trace on SRVR1 to capture what users are specifically using tables t1 and t2 . How to setup trace for this?
May 23, 2016 at 12:23 pm
Trace is being deprecated. You are better off using extended events if your version of SQL supports them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 23, 2016 at 2:50 pm
We cannot use extended events as it is not supported. Please let me know how to set up the trace
May 24, 2016 at 6:37 am
There's not a way to specifically trace only actions against a given table. Instead, you can set up a trace that captures all T-SQL code (SQL Batch Complete is the event you want) against a database and then you search through the string for the existence of the table. You would also need to set up RPC Complete to capture any stored procs that reference those tables. The key is to set up the filters, first by database, then by the two descriptions above. The best way to set up a trace is to use the Profiler GUI to build it, then, script it out. Here's how you add filters to events. You have the events you need, so you ought to be able to get this set up on your own.
Just understand this, Trace is a very inefficient mechanism for capturing events because of how it filters. It captures all events and then filters after the capture, using the resources first and then releasing them. This is why it's strongly recommended to use extended events. I'm not clear on why you couldn't use extended events in this situation. You would be better off doing that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply