Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

TRUNCATE TABLE and ROLLBACK TRAN Expand / Collapse
Author
Message
Posted Saturday, February 4, 2012 8:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 23, 2014 6:08 PM
Points: 5,401, Visits: 7,514
Comments posted to this topic are about the item TRUNCATE TABLE and ROLLBACK TRAN


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1246951
Posted Saturday, February 4, 2012 8:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Nice question - good to know my memory is not failing me

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1246952
Posted Saturday, February 4, 2012 8:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Good question; there are a number of links you could have included in the explanation, but one of my favourites is this by Kalen Delaney:

http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1246953
Posted Sunday, February 5, 2012 7:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 13,295, Visits: 11,086
Easy one, thanks to Paul Randals DBA Myth A Day.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1246993
Posted Sunday, February 5, 2012 9:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
Thanks for the question, but please explain why:


No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode


isn't also correct?


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1247073
Posted Sunday, February 5, 2012 11:25 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Explanation
there is no documentation on a non-existant concern

I disagree. Here is the link: http://msdn.microsoft.com/en-us/library/ms191544.aspx

Transact-SQL Statements Allowed in Transactions

You can use all Transact-SQL statements in an explicit transaction, except for the following statements:
ALTER DATABASE
ALTER FULLTEXT CATALOG
ALTER FULLTEXT INDEX
BACKUP
CREATE DATABASE
CREATE FULLTEXT CATALOG
CREATE FULLTEXT INDEX
DROP DATABASE
DROP FULLTEXT CATALOG
DROP FULLTEXT INDEX
RECONFIGURE
RESTORE

...
UPDATE STATISTICS can be used inside an explicit transaction. However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

Based on this article, one can conclude that a TRUNCATE TABLE statement can be rolled back.
Post #1247086
Posted Sunday, February 5, 2012 11:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 13,295, Visits: 11,086
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:


No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode


isn't also correct?


Because you can rollback a transaction with a TRUNCATE TABLE statement?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1247089
Posted Monday, February 6, 2012 12:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
Koen Verbeeck (2/5/2012)
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:


No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode


isn't also correct?


Because you can rollback a transaction with a TRUNCATE TABLE statement?


Tsk, thanks Koen....
Slow Monday morning for me....ignore me for the day ...


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1247099
Posted Monday, February 6, 2012 12:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 13,295, Visits: 11,086
Henrico Bekker (2/6/2012)
Koen Verbeeck (2/5/2012)
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:


No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode


isn't also correct?


Because you can rollback a transaction with a TRUNCATE TABLE statement?


Tsk, thanks Koen....
Slow Monday morning for me....ignore me for the day ...


Okidoki




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1247106
Posted Monday, February 6, 2012 12:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 4,023, Visits: 5,324
Thanks for the Back-to-Basics question, Craig.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1247108
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse