Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Tempdb data file fills up very often Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1498404
Posted Wednesday, September 25, 2013 8:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1498405
Posted Wednesday, September 25, 2013 8:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 42,458, Visits: 35,518
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 2008, MVP
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

Post #1498413
Posted Wednesday, September 25, 2013 8:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:46 AM
Points: 378, Visits: 238
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.
Post #1498422
Posted Wednesday, September 25, 2013 9:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 42,458, Visits: 35,518
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 2008, MVP
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

Post #1498436
Posted Thursday, September 26, 2013 9:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1498903
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse