SQL Server Performance Forum – Threads Archive
default filegroup is full
Hi Guys<br />I am getting the flowing error when I am trying to run a job which moves data. I am running sql server 2000, sp 3a, I have 4 files in temp db 50mb each (no auto grow) all 4 files have 47mb free space left. Temp db (current) size is 8192kb and log file size is 512kb. Any idea why I am getting this error? Thanks in advance.<br />Error =<br />Executed as user: sa could not allocate space for object ‘(system table id – 392323383)#%92 in database ‘tempdb#%92 because the default filegroup is full [sqlstate 42000] [error 1105] unable to open step output file. The step failed.<br /><br />Tariq[<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />
Why you have disabled AUTOGROW in TEMPDB?
Have you tested by enabling the AUTOGROW on TEMPDB? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
It was a recommendation by microsoft in our meeting. That Y we disable the auto grow and made 4 files
Also the recommendation was to split tempdb en 4 files?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
yes
Some times could be necessary to split tempdb, but I don’t agree with autogrow off.
If I were you I will set autogrow on but in MB, and set some value.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
Even if I enable autogrow I dont think that is the main cause of the problem.
Could be not. Now, if you use to stop and restart SQL service from time to time, then tempdb will be cleaned and almost empty.
If that is not the case, I think you will have problems with autogrow off.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
but my log file size is only 512kb
And log is also with autogrow off?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
no log is on on auto grow (unrestrict grow)
Well, if you don’t stop and restart sql to clean tempdb I don’t know what more suggest you. Wait for others members opinions. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
It looks like you database job needs more space than available free space and it is unable to allocate more space, because autogrow option is disabled. Is it 47 MB total free space or in each data file? You can try two ways:
Shrink the data files in TempDB, then you can get little bit more space.
Add one more data file to the file group, if you have disk space. Do you remember any specific reason, microsoft recommends to disable the autogrowth? I can expect one reason that, you will easily trackly the problems like this. Let me know
well its a long story why MS suggested us to make 4 files. I will post that later may be tomorrow since I have to catch a flight BUT I will post the reason tonight.
Sorry for the delay. stop and restart service solved the problem
]]>