SQL Server Performance Forum – Threads Archive
Insert,Update,Delete Logging or TrackingHi, What is the most effective way of logging or tracking any insert,update,delete execution? For example, I issued this statement from Query Analyzer or via asp (or any app):
DELETE FROM mytable I want to see when did the execution happened and who initiated it. Is SQL Server capable of doing this? Or should I look for a third party SW? Jon M
SQL Server comes with a tool called SQL Profiler which logs all activity on a given sql server. This will show you sql statements being executed, along with the client hostname and sql/NT username. I recommend running profiler on a workstation and pointing it to the server you want to trace, rather than running on the server itself, also, profiler naturally requires a small overhead which you should minimise: ie dont log data youre not interested in. You can also configure various filters to narrow down the log, and equivalent traces can also be created programatically (see sp_trace_create in Books Online)
Profiler is wonderful for this if you are trying to do analysis work. It helps figure out what is causing bottlenecks, identify inefficient code etc. Its worth looking into.
If you are looking for a permanant logging of all data modification, then you might have to look towards a log reader tool (provided you are looking things). Or another option is to put a trigger on every table and force a log on each data modification. I guess it depends on what you are trying to acheive. Are you looking to improve performance? Are you looking to log for audit/security reasons? Etc. Chris
Thanks for the replies.<br /><br />Yes SQL Profiler will work for me specifically the ‘SQL<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted’ Event. I just have to filter the TextData like ‘%delete%’,’%update%’,etc.<br /><br />Chris, This is for audit purposes.<br /><br />Thanks again guys.<br /><br />Jon M