Is it possible to rollback truncate

Active 1 year, 11 months ago. Viewed 24k times. Improve this question. Jonathan Leffler k gold badges silver badges bronze badges.

What exactly are you asking? Where did you read that it is not? You have a bunch of questions that have been answered and has obviously helped you, but you have not accepted them. Please do else people may be not be inclined to help you — Sathyajith Bhat. Once upon a time, the question was tagged with the oracle tag as well as the sql-server tag. A few days after the question was asked, the oracle tag was removed. Add a comment. Active Oldest Votes. Improve this answer. Remove From My Forums. Answered by:.

Archived Forums. This is the place for advice and discussions 0 1. Sign in to vote. Saturday, March 16, AM. Saturday, March 16, PM. Tuesday, July 30, AM. I dont have Adventureworks DB, so created some dummy table.

Conclusion: the overhead to recreate a table and do not make errors could be too much, even if it would be one millisecond faster I doubt, since the SQL Server does not really drop local temptables, that are used in stored procedures.

Truncate requires elevated privileges as it also resets any seed columns. ALTER is required on the table or the schema. I don't know exactly if this is the real behavior, but when I had to write a rollbackable TRUNCATE I would only log, that tbl1 had the pages and to plus pages for its index. Then I would put an exclusive lock on those pages to prevent them from beeing overwritten , unassign them so the table seems to be empty since there are no more pages in it or its indexes.

To verify this I don't have time at the moment , you could run create a table as copy of another including content , let you show, which pages are used by this table, start a transaction, run a TRUNCATE and fill the table again. After that it should not have reused the same pages but assigned some new ones. No, truncate just deallocares the pages and logs these deallocations.

If you rollback inside the transaction the page allocation are re instated. His passion focuses around high availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned long-term database administration, consulting, and entrepreneurial ventures working in the financial and healthcare industries.

When he is not on the clock, or blogging at SQLHammer.