Oh my! Phew! I forgot to share my "original proof"! Red Stripe!
Hehe.


SET IMPLICIT_TRANSACTIONS ON
USE test_edwarde
GO
TRUNCATE TABLE EMP3
ROLLBACK
SELECT * FROM EMP3





Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Dr. Eric J. Smith is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Damiani Paul
[mailto:mssqldba-ezmlmshield-x26387158.[Email address protected]
Sent: Friday, February 29, 2008 11:00 AM
To: LazyDBA Discussion
Subject: RE: RE SQL Server Truncate Table

I'm new here and maybe I'm oversimplifying, but isn't wrapping a
truncate up in a transaction just a bit of SQL "sleight of hand"?

Would one be able to rollback a truncate in a session with
implicit_transactions set?

Paolo Damiani
International Specialty Products



-----Original Message-----
From: Jeremy Greaves
[mailto:mssqldba-ezmlmshield-x63588365.[Email address protected]
Sent: Friday, February 29, 2008 10:48 AM
To: LazyDBA Discussion
Subject: RE SQL Server Truncate Table


Truncate is not supposed to be able to be rolled back, as it deallocates
the data files and makes their extents available for writing over. The
actual deallocation is logged in the transaction log however, but
technically TRUNCATE statements are not available for rollback. However
if you put the truncate statement within a BEGIN TRAN - (ROLLBACK TRAN)
COMMIT TRAN block, then of course that sessions activity is capable of
rollback. However, if you FUBAR by the use of TRUNCATE and have a
backup.... Is this a trick question?
Jeremy Greaves
Fenris Software Consulting, Inc

---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page