March 30, 2016 at 8:34 am
Hi
I have been asked to help determine what applications are hitting a particular table. Somehow the data from this table updates
SAP but no one knows how this is happening. I have looked at various dmvs and nothing I have recovered has been helpful. So I am wondering if I setup and Extended Events session to run for a 24 hour period I may capture something but I am new so I'm not sure what to capture that would be of help. Would any of you have insight into what I need to capture?
Thanks
Kathy
March 30, 2016 at 11:58 am
First, I'd determine if there were stored procedures that could, conceivably, be calling that table. If there are any, it complicates this slightly.
The events you want are sql_batch_complete and rpc_complete (if you have procedures). Extended events are a great choice for this, but, you could capture a lot of data, so you want to put filters in place to minimize that. I'm going to assume that all you can filter on is database and the table itself. You can easily add the database name or the database id to the filters for your events. Then, in sql_batch_complete, you'll need to add a LIKE clause and put in the table name you're interested in. I'd only use the table name, not the schema (unless you have duplicate names across schemas) just to allow for badly written queries. In rpc_complete, you'll want to list the stored procedures individually by name using an OR clause (the default is AND).
That should do it.
"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 30, 2016 at 2:02 pm
Thanks Grant--I followed your advice and we found the application that was updating SAP!
March 30, 2016 at 6:10 pm
Excellent. Glad things worked out.
"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