Monitor Truncate statements is this possible?

  • Is there a way to track a truncate statement without a trace. Can there be a trigger set up or something. We have DDL events being monitored but we have been researching this specific task on truncate table

    Thanks in advance

  • As far as I know, there's no DDL trigger that can pickup a TRUNCATE, unfortunately. You'll find the list here:

    http://msdn.microsoft.com/en-us/library/bb522542(v=sql.105).aspx

    Now, as a workaround, you could create an instead of trigger and check the rowcount of deleted vs. the table and if they match, fire an event to your logging system.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • You can use extended events to watch for the truncate statement. That's how I'd do it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant - Can you please guide on which extended events

    Abhijit - http://abhijitmore.wordpress.com

  • The statement completed events, sp_statement_completed or sql_statement_completed. You'll just need to ensure you put a filter in place to ensure you're looking at the right data. Otherwise you'll collect way too much information. Here's the basics on how to use extended events. You're going to want the predicates to filter down the information you capture.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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