SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb data file fills up very often


Tempdb data file fills up very often

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5356 Visits: 4076
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
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18922 Visits: 14900
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88821 Visits: 45284
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
AlexMcCo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1373 Visits: 422
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88821 Visits: 45284
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87874 Visits: 41124
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search