Post reply

TEMPDB sql alwaysON

  • March 17, 2017 at 5:29 am

    #319677

    Hi all.

    Recently we had an issue in our production environment, where we run out disk space for tempdb.
    The problem was fixed, but i'm curious why this happened

    This is the scenario:
    We have 2 sql server instances configured in a alwayson availability group (SQL server 2014 SP 2).
    The servers are configured with synchronous availability mode and the secondary replica is configured to accept readonly connections.

    When the problem ocurred,we where notified that tempdb disk on both servers were out of space.
    The DBA on call found an open transaction on primary server, that was running for 12 hours (a simple update statment on a single row).
    Without a deep investigation, this transaction was killed and the tempdb on both servers were shrunk.

    But now we're wondering:

    why tempdb had to growth on both servers?
    As far as i know, on secondary replica, the transactions are mapped to snapshot isolation level.
    But the transaction were opened on the primary. Why the secondary tempdb was huge?
    The same question came up to the primary: the transaction was running against user database, and was not using the tempdb.
    Why the tempdb had to growth on the primary server?

    Thanks,

    Luiz

  • March 17, 2017 at 6:45 am

    #1933731

    When you enable a database as a readable Secondary in an AlwaysOn Availability group, row versioning is automatically implemented and applies a 14 byte overhead on each row that is modified. In fact all isolation levels are transparently mapped to the snapshot isolation level to avoid redo thread blocking. Without this, report workloads could possibly interfere with the redo thread process.


    Any Readonly workloads running on the secondary databases will have temporary statistics generated and these are stored in TempDB. This could potentially increase TempDB usage as you are seeing, this is why it is important to carefully size the TempDB based on the usage it will incur.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 2 posts - 1 through 1 (of 1 total)

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