Home Forums SQL Server 2012 SQL 2012 - General Interaction between the DB size, DB log size, tempdb size and tempdb log size RE: Interaction between the DB size, DB log size, tempdb size and tempdb log size

  • July 3, 2015 at 5:55 am

    #1810184

    Mark Dalley (7/3/2015)

    How do the sizes of a database, the size of tempdb, and the sizes of their respective logs, interact?

    They don't really.

    A user database's size is based on the data in it. A user database's log size is based on the size of the largest transactions and on the time between log backups.

    TempDB's size depends on the usage of temp objects (temp tables, table variables, work tables) as well as on sort and hash spills, the version store and a few other things that use TempDB. TempDB's log size depends on the largest transactions done in TempDB.

    There's no formula that relates a user database's size to the size of it's log or the size of TempDB.

    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