How can I capture update query session details?

Question

Post reply

How can I capture update query session details?

Joe Doherty-260822

SSC Eights!

Points: 915
More actions
November 2, 2007 at 6:57 am

#376627

Hi guys,
A column in my table is being updated and I would like to find out what process, host, user, etc. is performing the update.
I have added a trigger to the table so that when an update occurs I am able capture the session details into a table, however the session details that are being returned are those of the actual trigger rather than those of the original query.
The statement that I am using to capture the session details is...
select * from sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) where session_id = @@spid
There is a parent_session_id returned in the above which I hoped might give me some pointers but this is always NULL.
I have been working my way through the system dynamic views in the hope that one of them will provide the information I am looking for but so far I've had no luck.
Because the system I am troubleshooting is web based the actual server and user details will be those configured as service accounts for the application but if I am able to show the developers the original query that was used for the update I'm sure they will be able to provide valuable information as to which component it is coming from. I imagine SQL will also be able to tell me which host is sending the query. With these two bits of info I'm sure I'll be able to track it down.
Our suspicion is that an old component is running somewhere and I'd like to be able to track it down. Perhaps I've spent too much time looking at this but I'm coming to the conclusion that it may not be possible from the SQL back end, which I am very surprised at.
I have tried capturing every session that was running at the time of the update but there's simply too much info to deal with. I even searched through these results but there's so many different possible combinations that an update statement might take form that it quickly became a worthless exercise.
Perhaps one of you can resolve this for me. Any ideas?
Regards,
Joe

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mahesh_sqldba SSC Journeyman Points: 91 More actions · Answer 1

you can use the following query to get the list of functions and sp that are executed against that

table

SELECT so.name AS FuncProcName, t.TABLE_NAME AS TableName, sc.text AS Definition

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id = so.id

INNER JOIN INFORMATION_SCHEMA.Tables t ON sc.text LIKE '%'+t.TABLE_NAME+'%'

WHERE (so.xtype='FN' OR so.xtype='P') AND so.category=0

AND t.TABLE_NAME = 'Account'

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 2

Thanks for your reply mahesh_sqldba.

Unfortunately the update query is very likely to be some dynamic sql and not a sproc or function, however I have added your script to my toolset. I'm sure it will come in handy at a later date.

Joe

mahesh_sqldba SSC Journeyman Points: 91 More actions · Answer 3

Did you try question profiler with filtering?

and another option i think you can try would be sysprocess table.

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 4

Yes i did try Profiling but I cannot guarantee that my filtering would cover all eventualities. There are so many ways you can update a value in a table I didn't feel confident that I was covering myself.

I think I may have to resort to this method if my problem isn't resolved.

Many thanks for your reply.

Joe

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 5

If you are trying to profile (which sounds like the best option from what you described above) any time a tables data is changed your best bet is going to be filtering on any SQL statement with "UPDATE" and your tablename in it. I can't think off the top of my head of any other way to update a table.

Also you may be able to narrow down the time frame your updates are comming in by using your existing trigger. If they are all comming in in one specific time range .. say 10-12 pm or something then you can just trace that time period.

Good luck

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]