Initial size for TempDb data and Log file?

Question

Initial size for TempDb data and Log file?

Mani-584606

SSChampion

Points: 11788
More actions
September 12, 2011 at 5:33 pm

#243144

Hi,
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

Mani,

It depends on how your tempdb is used. As a rule of thumb, It is usually advantageous to make several tempdb data files and place them all on the same tempdb drive. tempdb log only needs a single file. Size the data files to be all the same size and be sure to not auto shrink them.

So if you have 50GB, you'll probably want to leave some space free on the drive, so maybe 3 files of exactly 10GB? Or 5 files of 5 GB each? Something like that. The number of files depends on the number of CPUs you have on your system, with 1 file for every 2 CPUs, or 1.5 CPUs, or 1 CPU. Depends on who you talk to, but a few files would be good to start and it'll be in the ballpark, then you can monitor tempdb usage from there to ensure there are not page allocation waits on GAM/SGAM pages in tempdb.

Brent Ozar has some great scripts to help determine tempdb allocation contention, and Robert Davis has a nice PowerPoint slide deck about tempdb that you can google.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

crazy4sql SSCoach Points: 19590 More actions · Answer 2

In addition to what Jim said, configure datafile autogrowth in MB and logfile growth in %.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

All auto grow should be configured in MB, especially the log. A % can cause nasty surprises with larger files, and it's the log that takes longer to grow and usually the one that the growth is time-sensitive.

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

idokle Old Hand Points: 389 More actions · Answer 4

Never ever use % on log file or DB...

I just had my first problem with that. I have 2 companies with the same software that we provide and both have the same hardware same configuration on tempdb and all databases but for some reason one company was having problems at the peek work load.

It drove me crazy for 3 days. The server would just stop doing anything for about 3 min and then come back to normal like nothing had ever happened. Well after 3 days i found out that the most used DB on the server on one company had a data filegrowth 200 MB and log 200Mb and the other company was 1Mb on data file and 10% on log file.

Well thats all good when your DB is 100Mb but in my case the log file was 21gb and 10% on that is about 2.1Gb and it was stopping everything until it got the space. Imagine if i had the 10% on my data file which is 48Gb.

anyways never ever use % on data or log files.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

sharepoint 2010 does not seem to be a big user of tempdb. I have one installation with an initial size for tempdb of only 200mb and there has never been a growth.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please note: 2 year old thread.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 7

<sigh>

:doze:

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

lsalih SSCertifiable Points: 6143 More actions · Answer 8

I know this an old post, but just going throw the comments and trying to get an answer here. I found that Microsoft does not say anything about using percentage autogrowth for tempdb, all it says to allow automatic auto grow, http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

Any thoughts about what type of file growth increment to use?

taulpall SSC Journeyman Points: 99 More actions · Answer 9

the intention with auto growth is to intervene before it automatically grows. the auto growth is just a stop-gap emergency measure

melthedba SSCommitted Points: 1630 More actions · Answer 10

Regarding the type of file growth increment to use, it depends upon your database traffic. Ideally, you should not have to continuously have your database autogrow so if your autogrowth increment is set at 50MB, but it's growing every hour, then drastically increase your autogrowth increment. I always start at autogrowing 1GB because the databases I work with are usually not smaller than 1GB.

You can see how frequently your database is growing by going into the Disk Usage report as provided as a canned report in SSMS.

chuck.hamilton SSCommitted Points: 1537 More actions · Answer 11

I realize this is a very old thread but here is my .02 having managed numerous SharePoint databases. After all someone may still end up at this thread looking for tempdb sizing help.

If you plan to run DBCC CHECKDB on the SharePoint Content databases (you are planning to do this right?) size the tempdb to 1.25 x the size of the largest database.

Best practice for tempdb is always to disable autogrowth and presize the files to whatever you need and make sure all files are sized the same in order to keep allocation across the files even.

Start with 4 data files and increase if you see GAM or SGAM latch contention on any of the tempdb files. This can be seen in the sys.dm_os_waiting_tasks DMV where the wait type will be a page latch wait, and the resource will be a file in database 2 (tempdb).

ScottPletcher SSC Guru Points: 100947 More actions · Answer 12

Never disable autogrowth on tempdb -- it could bring the entire instance to a waiting state. Instead, put alert(s) into place to make sure you are made aware if/when tempdb data file(s) does ever autogrow.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

ScottPletcher (1/10/2017)
Never disable autogrowth on tempdb -- it could bring the entire instance to a waiting state. Instead, put alert(s) into place to make sure you are made aware if/when tempdb data file(s) does ever autogrow.

Amen to that. I don't know how the idea disabling autogrowth on TempDB ever became a so called "best practice".

--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)

chuck.hamilton SSCommitted Points: 1537 More actions · Answer 14

That's simply not true. An instance will not hang because you filled the tempdb database. Queries will fail and the instance will throw errors like 1105, but it most certainly won't hang.

If you do what you say and enable auto-growth for tempdb, be sure to limit the max size of the files or one run-away query can cause everything to hang. Consider what happens if that run-away query keeps auto-growing tempdb and it fills the disk leaving no room for your user database log files to grow. Now enabling auto-growth on tempdb (without setting a reasonable max size) has actually caused the instance to hang.

Best practices for tempdb are well documented. Put it on it's own drive, size all files equally, and preallocate their sizes (equally!) to fill that drive. If you have more than one file, setting auto-growth on any of the files will cause them to grow unevenly resulting in unbalanced use of the files and potentially resulting in GAM/SGAM contention on a busy server.

MS best practice for tempdb is 1 file per CPU up to a max of 8.