March 26, 2013 at 11:18 am
Is there a way to run a SQL Trace on a specific table? I've got a table in the production environment, which keeps changing values in certain column, and I'm trying to find what is causing the change.
March 26, 2013 at 12:25 pm
I probably would not use Trace for this one. Consider adding a DML trigger temporarily to capture the inserted or deleted tables and the user making the changes to an audit table.
If you're on Enterprise Edition you could also consider temporarily leveraging a Database-level Audit or Change Data Capture.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 4:37 pm
Never ran those before, but it's worth a try I guess. Would tracing a single table be that much of a load onto the server? This table barely gets used, and is very small in size.
March 26, 2013 at 7:01 pm
DVSQL (3/26/2013)
Never ran those before, but it's worth a try I guess. Would tracing a single table be that much of a load onto the server? This table barely gets used, and is very small in size.
The thing with Trace, and where the other technologies I mentioned outshine it, is that you cannot key onto a specific table. You key onto Events, and then you filter from there. In your case you would likely need to Trace the SQL:StatementCompleted and SP:StatementCompleted Events and then add a filter to essentially say WHERE Database = 'YourDatabase' AND SQLText LIKE '%YourTableName%'. Finding accesses to your table this way would be horribly inefficient and could add significant load to your system. The reason it would add load is because each Event has to be evaluated and then the filter is applied. With the two Events I mentioned, that means every single SQL statement entering your system has to be evaluated to see if it was destined for your database, and then whether the SQL text contained your table name.
With Audit and Change Data Capture (CDC) you actually do key onto a specific table so these methods are cleaner and have much less impact on your system
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 6:54 am
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.
"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
March 27, 2013 at 7:21 am
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.
I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 9:09 am
opc.three (3/27/2013)
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?
Oops, yeah, for 2008 you don't have those predicates. Jonathan has a suggestion here how you can still use ExEvents to get the job done though.
"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
March 27, 2013 at 9:55 am
Grant Fritchey (3/27/2013)
opc.three (3/27/2013)
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?
Oops, yeah, for 2008 you don't have those predicates. Jonathan has a suggestion here how you can still use ExEvents to get the job done though.
It's funny how Jonathan's name comes up when looking for help with EE. I ran into that same thread when tracking my aforementioned Snapshot-auditing issue: http://www.sqlservercentral.com/Forums/Topic1395766-1550-1.aspx
I ended up giving up on EE for adding auditing to my snapshot and ended up going with Trace. I was seeing some erratic beahvior with the sqlserver.lock_acquired event plus the solution Jonathan proposed where we would use the sqlserver.sql_text and sqlserver.tsql_stack actions was a little on the heavy side for the instance I would have been adding it too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 12, 2013 at 1:03 pm
So, I've looked into DML Triggers, and it seems like the way to go, but the only thing I need is to capture actual syntax or commands being executed on this table, a bit like the SQL Profiler does.
April 12, 2013 at 1:56 pm
inside the trigger, you can capture the first 4000 chars of the command text:
inside a trigger, you can do this, (DBCC INPUTUFFER can be used by users agasint their own spid)
--################################################################################################
--because dbcc inputbuffer is limited to 4000 chars, you may need to combine this witha DML trace
--################################################################################################
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo
FROM @buffer
Lowell
April 12, 2013 at 6:08 pm
I think something is missing. It is telling me that I must declare @LASTCOMMAND as well.
December 29, 2015 at 1:57 pm
I think you could use the "Audit Database Object Access Event" under security audit and apply a filter for object name as the table name you want.
_____________________________________________
December 29, 2015 at 2:17 pm
Please note: 2 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply