When data are actually deleted from pages.

Question

We know that truncate statement deallocates the pages allocated to a table. But the data still exists in those  pages.

As per my knowledge , sql server only removed the entry of such pages from PFS pages so that they cannot bw allocated to other table.

Please help me to know when actually data gets deleted from the pages which are actually orphaned(deallocated from a table).

Since truncate statement can also be rollbacked, it means the pages which all deallocated must also exists. So when can we say that the truncate statement can not be rollbacked i.e., the dellocated pages no longer have the data.

Please help. 

Answer 1

TRUNCATE will deallocate data pages so that the space can be reused by other objects. All data in the table are logically deleted when the TRUNCATE is committed.  The operation is efficient because the individual data pages don't need to be changed and the actual page deallocation can occur in the background.  There will be no "orphaned" pages due to TRUNCATE.

I don't know what you mean when you mention "the truncate statement can not be rolled back".  TRUNCATE can be rolled back (or committed) so it behaves as an atomic "all-or-none" operation.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 2

Since truncate statement can also be rollbacked, it means the pages which all deallocated must also exists. So when can we say that the truncate statement can not be rollbacked i.e., the dellocated pages no longer have the data.

Please help. 

Truncate statement can be definitely rolled back if run within a transaction. What you are asking is mentioned in Dropping and Rebuilding Large Objects

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 3

Looks like it happens  when ghost cleanup taks is running

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Thanks for your reply Dan, I wanted to know at what point of time these deallocated pages are available to be allocated to other tables.

Because I think these deallocated pages are not ready to be allocated just after the we run truncate statement, pages still contains data even after they are getting deallocated from a table.

Answer 5

Because I think these deallocated pages are not ready to be allocated just after the we run truncate statement, pages still contains data even after they are getting deallocated from a table.

You are right that the old data in the pages remain until they are overwritten during reuse.  But that is just an implementation detail since the pages are unallocated and available shortly after TUNCATE.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com