Method to capture TRUNCATE operations?

Transact-SQL
1

Hi,

Developing greenfield DWH with 2000+ separate daily loads. Many ODI (not SSIS) ETL loads are TRUNCATING a staging tbl (don't know tbl names) before the INSERT. Problem is many tbl have Clustered Column Store indexes applied which suit APPEND only data modification patterns and feel the TRUNCATE then load on CSS is hurting performance. (my testing implied inserts only do not carry too much overhead above a HEAP, but the TRUNCATE is bad)

Currently have no access to the ODI source code and unless I trawl through PlanCache/sp_WhoIstactive ect I cannot identify which tables are flushed and filled nightly. Is there a method to capture all the TRUNCATE within a fixed time?

Seen articles around capturing DLL's, audit feature and so on but did not have confidence to test. Look forward to your thoughts.

2

Do you have the permissions/access to set up a trace? If so you could set up one with filtering for TRUNCATE.

3

You could also capture this using an Extended Event session

4

This may help:

https://www.linkedin.com/pulse/20140918135022-2867075-how-to-find-out-who-truncated-your-table

1 Like