0

I have a SQL Server table where rows randomly get deleted. I want to check what is causing this as I see no issues from the application side.

I did create an after delete trigger and I am able to see which rows are being deleted, but that is still not helping pin-point on why some records are being deleted.

I am wondering if it is possible to log/track which executed SQL statement are deleting the rows from my table.

Please note I am using SQL Server 2016 Express edition.

0

2 Answers 2

3

You can use the help of SQL Profiler ..

Inside the profiler you can choose the events against your server..

enter image description here

enter image description here

enter image description here

4
  • Thanks so much, that is exactly what i was looking for, do you know if you can target a specific database or table
    – user1704812
    Commented Aug 26, 2016 at 16:14
  • you can specify the database..please see the updated answer.. Commented Aug 26, 2016 at 16:19
  • Thanks so much, what about a particular table in that database, or is that asking too much lol
    – user1704812
    Commented Aug 26, 2016 at 16:54
  • i think in the objectName you can specify your table.. :) Commented Aug 26, 2016 at 17:03
2

You can't use triggers to trace queries

You can use Extended events to track DML against a particular table.They are light weight in nature and you also can use them to log to disk for later analysis..

3
  • Thanks for your answer, but it appears Auditing is not available for Express edition
    – user1704812
    Commented Aug 26, 2016 at 14:41
  • 1
    @user1704812:This is not auditing,these are extended events,i don't they have edition restrictions..technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx Commented Aug 26, 2016 at 14:50
  • 1
    Yep easiest way might be to create a trigger then use extended events to fire when the objectid of the trigger shows that it has been executed. And capture the TSQL call stack stackoverflow.com/a/3149717/73226 Commented Aug 26, 2016 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.