May 1, 2006 at 12:08 pm
Hi,
I have 3 files added to tempdb database 1 mdf and 2 ndf files. due to space issue. but when the job runs mdf file is filled(completly full) and still space is available in ndf files but job is getting failed saying
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..
why sql server not using space from other files(.ndf). plase advice me.
May 1, 2006 at 12:23 pm
Did you leave the filegroup as primary? If not, it won't fill the ndf files.
Michelle
May 1, 2006 at 12:28 pm
all the files are in primary filegroup.
May 2, 2006 at 1:05 am
And is PRIMARY the default filegroup?
David
If it ain't broke, don't fix it...
May 2, 2006 at 3:05 pm
The round-robin/proportional fill doesn't work right for tempdb unless all the files are the same size. If the file that the server chooses to place an object on is not large enough, as that object increases in size it can run out of room even if there is room left on the other files. All files should be the same size, and all either allowed to grow, or large enough to accomodate the largest operational growth encountered.
May 3, 2006 at 3:36 am
I'd also advise a service restart after you've defined your tempdb data files to re-initialsie tempdb, it should work correctly then.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply