April 3, 2012 at 9:00 am
Hi, input required for this issue.....
Am getting "Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." errors fairly regularly.
We have never captured what is causing the issue, so its difficult to say "tempdb requires xxMB of space blah blah". Until then I'd just like to improve whats currently on offer. Here is the breakdown on the files given to TEMPDB:
File, Filegroup, Size, Autogrowth
------------------------------
tempdev,PRIMARY,3.4GB,200MB
tempdev_2,PRIMARY,1.6GB,200MB
tempdev_3,PRIMARY,1.5GB,200MB
tempdev_4,PRIMARY,3.5GB,200MB
templog,n/a,1.2GB,200MB
There are 8 CPUs so I am fairly happy with there being 4 files. There is also just over 5GB free on the disk volume, and i'm stuck with that for now. My questions are these though:
1) with the error message above, can that be caused by just one of the files filling up first and not being able to be expanded quickly enough?
2) the files are different sizes - would having them exactly the same (as recommended in a few places) allow a round-robin utilisation and so prevent the error, or is that more related where performance issues are concerned.
April 3, 2012 at 10:57 am
rarara (4/3/2012)
2) the files are different sizes - would having them exactly the same (as recommended in a few places) allow a round-robin utilisation and so prevent the error, or is that more related where performance issues are concerned.
I am not sure it will solve your specific problem, but I would definitely make all your data files the same size. Each file will autogrow independently though, so you'll want to monitor them so if one grows you can make provisions to manually grow the others and possibly change the initial sizes across the board.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 3:43 am
Is Instant Initialization enabled for the service account to allow the autogrow to be instantaneous?
Also if it was me I will use most of the 5GB still free (I presume only TEMPDB resides here), and distribute them evenly and not rely on autogrow.
Having multiple same size files will help performance, but it seems that your problem is a space issue.
I will also look at DMV's like dm_db_task_space_usage to find out which task(s) is allocating all the space and causing the problem in the first place.
April 4, 2012 at 10:20 am
I'd also recommend going to identical sizes and I'd recommend turning autogrow off and monitoring the space closely. If you do have dedicated drive space for tempDB I'd probably use it all as well. You really don't want the tempdb files growing if you can avoid it.
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
April 5, 2012 at 2:06 pm
Are you using SQL startup trace flag 1118 ?
If you are not then the additional data files for tempdb are not really buying you any performance improvements.
If you are using the trace flag then you need to make all of the data files for tempdb fixed in size with no growth whatsoever to reap any performance gains.
Also, you mention 8 CPUs ... is that 8 physical CPUs ? is that 4 dual core CPUs ? This does make a difference when you decide to use multiple data files for tempdb. The CPU/core to tempdb data file ratio can have rather 'dramatic' impacts on performance for a really busy SQL Server ('dramatic' can be either good or bad ... depending)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 5, 2012 at 2:23 pm
rudy komacsar - Doctor "X" (4/5/2012)
Are you using SQL startup trace flag 1118 ?If you are not then the additional data files for tempdb are not really buying you any performance improvements.
If you are using the trace flag then you need to make all of the data files for tempdb fixed in size with no growth whatsoever to reap any performance gains.
Also, you mention 8 CPUs ... is that 8 physical CPUs ? is that 4 dual core CPUs ? This does make a difference when you decide to use multiple data files for tempdb. The CPU/core to tempdb data file ratio can have rather 'dramatic' impacts on performance for a really busy SQL Server ('dramatic' can be either good or bad ... depending)
The latest things I've read say that T-1118 may not be needed on 2005+
http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
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
April 5, 2012 at 2:29 pm
Trace flag 1118 is still needed in order to distribute the load on the allocation maps of the tempdb otherwise all of that activity is in the first data file. It is definitely needed on 2005 for optimum performance. Don't know about 2008 vanilla - we do use it on 2008 R2 as well for our huge systems.
I would be interested in checking any links you have run across on this subject that may suggest that it is not needed ... if you could please oblige Jack 🙂
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 5, 2012 at 2:38 pm
The linke I posted all state that changes in 2005+ make it less necessary to use the trace flag, but that it can still be helpful if you are experiencing contention.
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
April 5, 2012 at 2:48 pm
I overlooked the links Jack - I understand.
Our site is in the circumstance where we absolutely need it for a few key applications.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply