Initial size for TempDb data and Log file?

Question

Initial size for TempDb data and Log file?

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

chuck.hamilton (1/10/2017)
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.

Hang, queries fail. Who cares? They're both bad.

Please provide the link to the MS document that says it's a best practice to turn off auto-growth on TempDB.

I can't speak for anyone else but I have only 16GB allocated to TempDB on a 100GB drive dedicated to TempDB. The reason why is because I expect there to be no TempDB autogrowth on the system because it was carefully and deliberately sized. If I get an autogrowth alert on TempDB, I want to have some time to determine if I should kill the offending SPID (almost always something I try to avoid) or hunt down the hog that caused the problem and get them to gracefully cancel the query. I can't do that if autogrowth is turned off.

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

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

chuck.hamilton (1/10/2017)
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.

If a db needs log space and can't get it, it can most certainly wait. You said "files", not "data files", implying the log too. Either way, both are wrong, and both should allow autogrow. A runaway task is a problem, but often a manageable one, whereas causing any type of delay or failure across the entire instance is much worse.

An alert gives time to deal with problem. To get the files back to the same size -- which will not really be that big a problem anyway -- you simply reallocate the smaller tempdb files to the larger size (or) perhaps shrink the larger data file(s) back to the original size.

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

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

On a side note, the original Op mentioned his TempDb was stored on his SAN. IMO, that is a waste of SAN and Backup resources. We put our tempdb on a local SSD array.

Wes
(A solid design is always preferable to a creative workaround)