What are the different ways to log the different DML operations that are being performed, along with the host performing them. I do not wish to use triggers as they are costly. I considered SQL Audit for a while, but they only furnish you with the SPID, that may be useful, but for a limited window. So SQL Audit feature won't bear any fruits in resolving this either. What are the other options available at one's disposal?
asked Sep 4, 2015 at 15:41
-
How frequently will these DML operations occur? You mention that triggers are costly and while that can be true, it is not often a big problem with DML. Your case could be different. Have you considered extended events or SQL trace (depending on version of SQL)? Or possibly if the DML operations occur within an application you could consider changing the application code to also log those operations. Some more specifics about what you are doing would probably be helpful here, I think.– mskinnerCommented Sep 7, 2015 at 0:13
-
These DML operations will occur quite frequently, I studied SQL trace and implemented it, it gives me the hostname that performs a DML operation, however it doesn't give me the DML command that was fired. Neither do I get the database on which the DML was performed. Also, in trace, the Database Id returned is '1' which corresponds with the master table, even when tables from other databases are modified!– Ritesh BhakreCommented Sep 8, 2015 at 13:58
Add a comment
|