Home Forums SQL Server 2014 Administration - SQL Server 2014 Transaction Log and TempDB Calculations RE: Transaction Log and TempDB Calculations

  • March 14, 2014 at 12:17 am

    #1697033

    alex.sqldba (3/13/2014)

    Evening All,

    Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.

    Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.

    Rouhgly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used? Is tehre a way to do this?

    Any help or thoughts is appreciated

    Al

    Rebuilding a clustered index can be done in a minimally logged fashion if done in the BULK LOGGED or SIMPLE recovery models. If you also do a CREATE WITH DROP EXISTING, it (according to BOL. I've not actually ever tested it) won't cost you much in the MDF file, either. The key will be, does this need to be done online or can it be done offline?

    As a bit of a sidebar, if the clustered index is already based on an INT IDENTITY, you shouldn't have to rebuild the clustered index unless you screwed up somewhere and allow IDENTITY INSERT to be turned on.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)