May 13, 2013 at 5:47 pm
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
May 13, 2013 at 6:31 pm
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.
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
May 14, 2013 at 4:12 am
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
May 14, 2013 at 6:19 am
Grant - Can you please guide on which extended events
Abhijit - http://abhijitmore.wordpress.com
May 14, 2013 at 7:14 am
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