Tempdb data file fills up very often

Question

Tempdb data file fills up very often

Viewing 6 posts - 31 through 35 (of 35 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

AlexMcCo (9/25/2013)
You do not need tempdb ndf file as large as you can make them. It is more than enough to put as 50 mb each. They will expand as much as needed during workload, just do not put hard growth restrictions.

i think you missed the logic of intial tempdb size posted above amy some senior members.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

AlexMcCo (9/25/2013)
You do not need tempdb ndf file as large as you can make them. It is more than enough to put as 50 mb each. They will expand as much as needed during workload, just do not put hard growth restrictions.

This is bad advice as growth is an expensive operation. ALL databases, including tempdb, should be set to a size that doesn't require growth and space should be monitored so you can manually grow them during times of low use.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

AlexMcCo (9/25/2013)
It is more than enough to put as 50 mb each.

Really? So my TempDB, which regularly has 200GB in use should drop down to 50MB every time I restart the server? Does that honestly sound like a good idea?

2001 called, it wants its file size recommendation back.

They will expand as much as needed during workload, just do not put hard growth restrictions.

File growth is an expensive operation. Operations needing TempDB will either have to wait or will fail while TempDB's growing. Why would I want that overhead during normal operation on a regular basis rather than starting TempDB at a size close to or over what I know it needs to be?

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

AlexMcCo Hall of Fame Points: 3546 More actions · Answer 4

We are talking about initial instance configuration. It might expand latter up to 10 GB depending on database size. You misunderstood me, to be frankly. And another thing is.. might be your code need to be optimised?

as in my case I have the instance with 800 GB databases (where 2 databases around 250 GB and few more sharing 300 GB each (mdf)) and my temp db is only 6Gb for the instance. Instance is used for production. So with your 200 GB tempdb .. of course it depends what your general mdf volume.. but you definitely having problem with your code.

It was just an example.

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*Brevity is the soul of wit.

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

AlexMcCo (9/25/2013)
So with your 200 GB tempdb .. of course it depends what your general mdf volume.. but you definitely having problem with your code.

I'm glad you can come to such a conclusion without knowing anything about the system....

Re that 50MB installation size, I don't recall a single time I've installed SQL in the last 4 or more years that I've set TempDB that small (well, once, a SQL Express instance). 250-300 MB is usually what I'll set and that only if I have no idea at all what is going on that server. If I do, then the initial size will be based on what I know about what's going onto that instance.

Bear in mind that TempDB is used heavily by CheckDB (it stores the facts there), used by the row version store (snapshot isolation, read committed snapshot isolation, online index rebuilds, triggers), used by index rebuilds when sort_in_TempDB is specified, plus work tables and spills. That's over and above the temp table/table variable usage.

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

AlexMcCo (9/25/2013)
You do not need tempdb ndf file as large as you can make them. It is more than enough to put as 50 mb each. They will expand as much as needed during workload, just do not put hard growth restrictions.

For all the reasons the others have stated, that's just not right. In fact, it's a pretty bad recommendation. TempDB should be sized like any other database so that growth is normally a planned event instead of taking you by surprise when you can least afford it. Correct sizing will also prevent the fragmentation that so many instances suffer.

And, as a bit of a sidebar, if you do have ndf files for temp, they should all be the same initial size and have the same growth settings as the mdf file.

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