Post reply

TRUNCATE TABLE on Mirrored DB

  • August 20, 2012 at 11:39 am

    #280027

    I truncated a table in my mirrored training environment on Principle side.

    From what I remembered, TRUNCATE TABLE [tablename] is not a logged opereation.

    Will this cause problems on the Mirror side?

    Thanks!

  • August 20, 2012 at 11:56 am

    #1527766

    TRUNCATE TABLE is logged. If you truncate the table on the principal side, it will get truncated on the mirror.

  • August 20, 2012 at 12:06 pm

    #1527775

    Truncate table is a logged operation (there are no unlogged data modifications in SQL). It can be rolled back, it can be mirrored. It's technically even a fully-logged operation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 27, 2012 at 1:55 am

    #1530221

    smallmoney (8/20/2012)

    I truncated a table in my mirrored training environment on Principle side.

    From what I remembered, TRUNCATE TABLE [tablename] is not a logged opereation.

    Will this cause problems on the Mirror side?

    Thanks!

    It is minimally logged operation but wrong to say that it is unlogged. Information is logged therefore it can even be rolled back if done inside a transaction where commit was no issued and a rollback was initiated due to some reason.

    Chandan

  • August 27, 2012 at 2:06 am

    #1530222

    Technically it's not minimally logged. It doesn't behave as minimally logged operations do. It's fully logged, just as drop table is fully logged. Both of them log just the page deallocations.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 27, 2012 at 2:15 am

    #1530226

    GilaMonster (8/27/2012)

    Technically it's not minimally logged. It doesn't behave as minimally logged operations do. It's fully logged, just as drop table is fully logged. Both of them log just the page deallocations.

    Thanks for using correct words for this situation and correcting my glitches.

    Chandan

Viewing 6 posts - 1 through 5 (of 5 total)

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