Why does truncate table put inside a transaction fail with ASE error 226

sybase-asesybase-ase-15.7

Spotted some answer excerpts on the question entitled "Why use both truncate and drop?":

"TRUNCATE is logged, and it can be rolled back.
…
Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase." answered Nov 8 '11 at 21:39

Attempted to add comment but original post has been protected hence this new Question as a workaround.

The above may be true for Microsoft SQL Server, it is NOT for Sybase – see ASE error 226 below

    1> BEGIN TRAN
    2>    TRUNCATE TABLE xyz
    3> go
    Msg 226, Level 16, State 1:
    Server 'ASE16', Line 2:
    TRUNCATE TABLE command not allowed within multi-statement transaction.
    1> ROLLBACK
    2> go

Furthermore – I almost agree with the notion that truncate before drop should make no difference…

  1. TRUNCATE TABLE on its own – involves some syslog activities (surprisingly)
  2. DROP TABLE on its own (without proceeding truncate)
    • deallocate the table pages within an "empty" BEGIN/END Xacts pair
    • updates sysobject, sysindexes system tables & their indexes & such internally generated DMLs involve logging within BEGIN/END Xacts pair

DROP TABLE on its own is marginally better as table page deallocation is not logged while truncate on its own seems to incur small amount of logging surprisingly.

SYSLOG Audit Trail : truncate on its own (as shown below – TX seq is based on the most recent 5 transaction being displayed)

    Log Record Type                          Op      TX seq
    ---------------------------------------- -- -----------
    ======>Checkpoint Record                 17           5
    Begin Xact                               0            5
       Delete Extent Log Record              77           5
       Direct Update/In Place Update         9            5
       Update Record for DOL Table           65           5
    End Xact                                 30           5
    ======>Checkpoint Record                 17           4

SYSLOG Audit Trail : drop table on its own (as shown below – TX seq is based on the most recent 5 transaction being displayed)

    Log Record Type                          Op      TX seq

    ---------------------------------------- -- -----------
    ======>Checkpoint Record                 17           5
    Begin Xact                               0            5
       Delete Extent Log Record              77           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       Deallocate Data Page                  21           5
       B-Tree Non-Leaf Delete                72           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       Deallocate Data Page                  21           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       B-Tree Non-Leaf Delete                72           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       Update Record for DOL Table           65           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
       B-Tree Non-Leaf Delete                72           5
       Delete Log Record for DOL Table       66           5
    End Xact                                 30           5
    Begin Xact                               0            4
    End Xact                                 30           4
    ======>Checkpoint Record                 17           3

Best Answer

As previously mentioned in the question, I came across an old post entitled "Why use both truncate and drop?" while searching for Sybase ASE handling of truncate table with its special attributes different from other so-called minimally logged Sybase Transact-SQL (T-SQL) operations (e.g. select into).

I noticed an extract in one of the answers which extrapolated, incorrectly, a Microsoft SQL Server feature (truncate table being handled inside a transaction rendering this as logged & suitable for rollback) as equally applicable in Sybase ASE.

I created a new question with a reference to the old one to clarify 2 points:

Point 1

  • Unlike Microsoft SQL Server, one cannot put truncate table inside a transaction in Sybase ASE
    • Doing so will hit ASE error 226 (see error text in the body of the Question)
  • REASON : truncate table does not generate any log records pertaining to the removing of data rows instead data & index (if any) pages are simply deallocated, pages are marked unused

  • In comparison, drop table updates system catalogs (sysobjects, sysindexes etc.) having taking care of the equivalent of truncate table in a tiny transaction log events (visible in the log record audit as an empty BEGIN Xact/END Xact bracket).

  • IMPORTANT : truncate table is unique in 2 respects

    • (A) it can be used in databases without their dboption "select into/bulkcopy/pllsort" being enabled first (unlike other minimally logged T-SQL commands)
    • (B) it does not prevent subsequent dump transaction command on the database (unlike other minimally logged T-SQL commands - the workaround of this restriction is to take a full or cumulative DB dump to reset it)

Point 2

  • The original Question/Answer refer to whether truncating a table first before dumping it would make any gain in performance, elapsed time etc. The consensus appears to a NO answer.
  • In Sybase ASE at least, while there is no significant efficiency gain to truncate table first, a closer examination of the transaction log activities seems to reveal a small log related overhead in executing the truncate table (this is absent in the processing of drop table without a proceeding truncate table as shown in the syslogs audit trails in the body of the Question).