Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Monitor Truncate statements is this possible? Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 5:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:42 PM
Points: 82, Visits: 660
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
Post #1452369
Posted Monday, May 13, 2013 6:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1452373
Posted Tuesday, May 14, 2013 4:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452483
Posted Tuesday, May 14, 2013 6:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 924, Visits: 714
Grant - Can you please guide on which extended events

Abhijit - http://abhijitmore.wordpress.com
Post #1452530
Posted Tuesday, May 14, 2013 7:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse