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 04, 2012 8:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 5,986, Visits: 6,932
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 04, 2012 8:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 5,472, Visits: 23,549
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 04, 2012 8:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
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 05, 2012 7:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 12,212, Visits: 9,192
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 05, 2012 9:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
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 05, 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: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
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 05, 2012 11:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 12,212, Visits: 9,192
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 06, 2012 12:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
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 06, 2012 12:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 12,212, Visits: 9,192
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 06, 2012 12:52 AM


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: Yesterday @ 8:33 AM
Points: 3,690, Visits: 4,822
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