Post reply

Estimating Tempdb Size and Log File Sizes

  • May 22, 2013 at 6:01 am

    #294316

    We have an Oracle Database with a size of about 80 GBs with 8 Redo Logs of 300 MBs each. Just wondering what would be a good starting point in SQL Server 2012 for this same database? I know there are more factors that go into trying to derive the Database Size but this is all I have right now. (I also will try to get the vendor input.) It is a new application (Student Information System for a large school district; about 60,000 students, 4,000 staff.) Is there some kind of guideline, such as set your tempdb to 40-50% of user database size and set log files to 25-30% of user database's data files (primary and secondary) size?

    Thanks in advance, Kevin

  • May 22, 2013 at 6:33 am

    #1617387

    No way to tell really. TempDB isn't the same as Oracle's redo space, so you can't use that as a basis.

    Tempdb is used for temp tables, table variables, some internal query processing, spills from memory, triggers, version store if you're using one of the snapshot isolation levels, and a whole bunch more.

    Best way honestly is to test the app out and see how big TempDB gets, that's gives your a lower bound on the required size.

    For transaction log, I would say as a minimum bound, 120% of the size of the largest index in the database. That's for index rebuilds. It's a very rough estimate, but better than nothing.

    Make sure you get the maintenance of the log right, I'm using an iPad so don't have all my usual bookmarks, I'm sure someone can point you at a log management article.

    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

  • May 22, 2013 at 7:00 am

    #1617408

    Thanks GilaMonster.

    Per your statement "No way to tell really. TempDB isn't the same as Oracle's redo space, so you can't use that as a basis."

    Just based on the Oracle DB sizes and Oracle redo log sizes I gave you, I was trying to get a feel for where I should start with SQL Server TempDB and Log File Sizes. (I believe Oracle's Temp Tablespaces would be similar to SQL Server TempDB and Oracle's Redo Logs would be similar to SQL Server Log Files.)

    Thanks again, Kevin

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

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