Post reply

Non-logged transactions in database-mirroring.

  • November 29, 2006 at 7:47 am

    #193287

    How does non-logged transactions is handeled in database mirroring? Are non-logged transactions cannot be shipped to secondary server in log-shippping?

    Any help is appreciated.

     

    Thanks,

    SR

    Thanks,
    SR

  • November 29, 2006 at 11:23 pm

    #674853

    What specifically are you thinking of when you say 'non-logged transactions'?

    As far as I know, in full recovery mode, all transactions are logged, and datbase mirroring only works in full recovery mode.

    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

  • November 30, 2006 at 7:33 am

    #674917

    Thanks for response Gail.How about if I do truncate table which is a non-logged transaction?

    Thanks,

    SR

     

     

    Thanks,
    SR

  • November 30, 2006 at 3:50 pm

    #675056

    You are taking the term "non-logged" too literally. When you truncate a table, it is logged. It logs a single record basically saying that it the table was truncated as opposed to a delete statement which would log a record for each row that was deleted.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • December 3, 2006 at 11:10 pm

    #675395

    As Robert said, truncate is logged. It's known as a minimally logged transaction (or at least that's the term I've heard). If it wasn't logged then you wouldn't be able to roll it back

    It's logged in pretty much the same way as a drop table is logged, as a DDL operation, not a DML.

    You can see it with the following (do this in a test db please). What you'll see depends on the version of SQL you're running

    CREATE

    TABLE Test (

    id int

    )

    GO

    INSERT

    INTO Test VALUES (1)

    INSERT INTO Test VALUES (2)

    INSERT INTO Test VALUES (3)

    INSERT INTO Test VALUES (4)

    INSERT INTO Test VALUES (5)

    INSERT INTO Test VALUES (6)

    GO

    SELECT

    * FROM Test

    GO

    CHECKPOINT

    -- to truncate inactive records in the tran log. Assuming simple recovery here

    GO

    SELECT

    * from ::fn_dblog(null,null) -- what's in the tran log before we truncate. In my case, 2 rows

    BEGIN

    TRANSACTION

     TRUNCATE TABLE Test

     SELECT * from ::fn_dblog(null,null) -- what's in the tran log after we truncate. In my case, 14 rows

    ROLLBACK

    TRANSACTION

    SELECT

    * from ::fn_dblog(null,null) -- what's in the tran log after we roll back. In my case, 22 rows

    SELECT

    * FROM Test -- still 6 rows in the table

    GO

    DROP

    TABLE Test

    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

  • December 4, 2006 at 3:20 pm

    #675648

    Thanks for nice explanation Gail.

     

    Thanks,

    Sree

    Thanks,
    SR

  • December 6, 2006 at 9:13 am

    #676273

    Clarification - minimally logged as in a truncate table, page deallocations are logged as opposed to individual rows being logged when using delete.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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