Post reply

Data Page Integrity After Truncate

  • November 27, 2007 at 2:18 pm

    #180856

    If the data pages are deallocated during a Truncate, then we rollback the command, how can we be sure that the data is still intact? Is it not possible that another process may overwrite?

    Any thoughts on this would be appreciated.

  • November 27, 2007 at 5:19 pm

    #754790

    Hi Carl,

    The pages and extents that are deallocated by the TRUNCATE are locked until the transaction commits or rolls back so that no other transaction can allocate and overwrite them. Try your scenario and execute an sp_lock after the TRUNCATE and you'll see what I mean.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • November 28, 2007 at 8:20 am

    #754999

    Paul:

    thanks for your excellent answer. Your example helped me understand the concept too.

    Carl.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply