February 12, 2016 at 10:20 am
I am trying to detect when a particular table is truncated in a database being operated on by 3rd-party code. I know that the operation is a truncate and I know it happens in the evening sometime (when I am not there to watch it), but I need to know exactly when it happens. (I have, of course, tried the approach of asking the 3rd party, but that didn't work.)
Truncate table does not fire DML triggers or DDL triggers, so triggers are out. Most of the posts I found about observing truncate are several years old and related to SS 2005 or SS 2000, so surely there's something now available? I looked into the audit feature but on a cursory observation, I couldn't figure out how truncate table is audited. I don't want an entire database audit. All I want is to observe one table and record when it's truncated.
I'd like to do this elegantly, but if there is no elegant approach, I suppose I could leave a procedure running that observes the table in a loop (with a brief wait period) and reports when a select returns 0 rows. While that won't always indicate a truncate on any table, for this particular table, it would. That might actually be a valid use case for NOLOCK as I wouldn't want to block the truncate.
Ideas?
February 12, 2016 at 10:27 am
Use an extended event session to monitor code that calls the TRUNCATE dbo.YourTable statement. Johnathan Kehayais has a great blog on Extended Events.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
February 13, 2016 at 2:48 pm
Second vote for extended events. This is lightweight and you can leave the session running.
February 13, 2016 at 2:49 pm
Sorry, meant to post this. We have a Stairway to help you get started: http://www.sqlservercentral.com/articles/134869/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply