Could not allocate space for object temporary system object: ... in database 'tempdb' because the 'PRIMARY' filegroup is full.
Question
Friday, July 4, 2014 4:10 PM
Using SQL Server 2008 R2. Tempdb resides on disks W and X both of those are 250 GB each. Nothing else resides on these disks. The recovery mode for tempdb is set to Simple and is split in 8 files and one log file. Each of the tempdb file is set for auto growth by 128 MB and max size is unlimited. The log file is set for auto growth by 10% and max size unlimited.
I get the following error at least once a week while running an ETL:
[SQLSTATE 01003] (Message 8153) Could not allocate space for object '<temporary system object: 335532118192937>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. [SQLSTATE 42000] (Error 50000). The step failed.
I have to then shrink the tempdb log and files to create space on the disks.
What is a permanent solution to this problem, I don't want my ETL to break due to TempDB diskspace full.
Thanks in advance.
All replies (2)
Friday, July 4, 2014 5:28 PM âś…Answered
Hi,
Permanent solution would be finding out query which is causing tempdb log file to grow out of proportion and fill it and I would like you to take help of below article
Troubleshooting out of space issue in Tempdb
Please change Autogrowth value for Log file from 10 % to may be 100 M. This is just a tentative value you need to study growth of tempdb to reach to correct value. You kept 128 MB autogrowth for tempdb data file is this value tentative or you just put it.
Below article can be referred for tempdb details
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.
Saturday, July 5, 2014 2:36 AM
It looks like that the data files for your tempdb are taking too much space. They are various reasons for this. Need to find the root cause and do some design change or tuning in ETL program. If it is not possible to change the ETL part. You may look at the 2 drives you have. Are you using one of them for data files and another for log file? Can the space be increased? Or redistribute btw the 2 drives, ie, make one 400 GB for data files and 100G for log file depending on the usage of both types of files? Search "Troubleshooting Insufficient Disk Space in tempdb" for more info.