tempdb space error message

Question

Post reply

tempdb space error message

mark.jr1

SSC Veteran

Points: 221
More actions
December 8, 2011 at 4:27 pm

#249390

We had an error regarding tempdb. Looks like there is enough space, but when I checked there are 9 data files in the primary file group. Primary datafile is 512 MB and has no autogrowth, while the others have same initial size, but have autogrowth by 128 MB and restricted growth to 2 GB each. The log file is restricted to 10 GB growth. Please let me know suggestions on what can be done.
Error message below
tempdb Could not allocate space for object 'dbo.SORT temporary run storage: xxxxx' 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 1

Please follow the articles in sequence.

Troubleshooting Insufficient Disk Space in tempdb

http://msdn.microsoft.com/en-us/library/ms176029.aspx

Capacity Planning for tempdb

http://msdn.microsoft.com/en-us/library/ms345368.aspx

Optimizing tempdb Performance

http://msdn.microsoft.com/en-us/library/ms175527.aspx

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

mark.jr1 (12/8/2011)
We had an error regarding tempdb. Looks like there is enough space, but when I checked there are 9 data files in the primary file group. Primary datafile is 512 MB and has no autogrowth, while the others have same initial size, but have autogrowth by 128 MB and restricted growth to 2 GB each. The log file is restricted to 10 GB growth. Please let me know suggestions on what can be done.
Error message below
tempdb Could not allocate space for object 'dbo.SORT temporary run storage: xxxxx' 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.

I have few points here:

1) Double check whether you really have enough space.

2) When you have multiple data files, it is recommended to use same size.

3) Table db.SORT does not look like a temporary table name. Usually tempdb is used for temporary objects. You may need to take a closer look at this table.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

I would concur with the other answers. Each member of the filegroup will be filled using a 'proportional fill' algorithm ie. turn-based fill, hence if one file is fixed in size and others are not, SQL Server may not recognise that and could return the error you're experiencing.

'SQL Server 2008 Internals and Maintenance' has an excellent chapter, 'Understanding TempDB', if you're interested in that sort of thing (I am).

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.