SQL Server TRUNCATE TABLE – Can It Be Overloaded?

sql servertruncate

I know it may look like a weird question…

I have a 3rd party db and services, and I want to monitor it by DML triggers on INSERT, UPDATE and DELETE of each table.

But alas, I have a problem when TRUNCATE TABLE is called, since it's not activating the DELETE triggers.

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows
TRUNCATE TABLE X; --truncate the empty table

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

I'll be happy if there are any other proposals too.

Is something like this possible?

EDIT

I don't want to disable the truncate I just want to monitor the data.

It is 3rd party DB, and it been used by services that I can't have control on, so I can't prevent the truncate.

I'm just trying to monitor changes and currently can't track the deletion of records caused by truncate command.

Thanks!

Best Answer

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows TRUNCATE TABLE X; --truncate the empty table

When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

You do not need a trigger for this. A TRUNCATE will remove all rows in the table, you know all rows are going to be removed in the process. No survivors.

If you would like to capture the event to know who truncated what or when, you can do this with SQL Audit, as shown by Mohd Sufian on SQL Ship.

I don't believe you can fire trigger from a Truncate, here is the DDL List on MSDN of what DDL events can be fired from a trigger.

You can also use extended events to capture Truncate events as Jonathan Kehayias shows on MSDN.

If you want to prevent users from running Truncate, you can create a view or another object with SCHEMABINDING. This prevents DDL statements from altering the table in question, which includes Truncate.

One last note about Truncate is that if you have an identity column, it will reseed the value. I have seen people use the RESEED function and Truncate together. They will capture the last identity used, store it into a variable table, truncate the table removing all records with minimal logging and resetting the identity column, then reseed the last incremental. Usually if you truncate though, you don't care about the reseed. (Not always true, but usually.)

I truncate primarily on staging tables or when I need to reset an instance for testing. I rarely truncate tables otherwise because I care about the referential data integrity and which rows are removed from the tables. When I care about neither, I truncate.

I would highly recommend to read more on both Delete and Truncate, they will achieve similar results but are two very different things.

Atul Gaikwad on MSSQLTips wrote a nice quick article on some of those differences.

Now let's look at this question:

Can I count the rows that will be affected by the truncate from a third party application?

Ideally, the truncate event is scheduled or it can be planned for. Even more ideally, you will be able to alter the code or add something in so it can log information for you. From your post, I'm going to assume you can't plan, schedule, or alter the code.

So how do you track what rows will be affected by truncate if you had to, with absolute certainty?

I personally would create two tables, a trigger, and a procedure.

The trigger would be based on Insert or delete tasks and it would insert into a table the number of rows affected, the table name, and the datetime. So if your process inserted 6 rows, the trigger would insert the value 6 indicating 6 new records flowed in. If you deleted 10 rows, it would insert a value of -10 indicating 10 records are now gone.

I would want to create an extra column for notes or events. I would want the audit or extended events to log to this table the day / time the truncate occurred. They won't be able to tell you how many rows, but it can tell you when and who. That's what's really important at this moment.

The second table would be an aggregate of the first table as I assume you may have many transactions in a day. This would tell you the net total of records at the end of the day and also tell you when truncates occurred. This means you would know how many records were in the table just prior to truncation, meeting your requirements.

The procedure would do the aggregation between tables and it would also perform cleanup work on the first table.

Related Question