tempdb space error message

  • mark.jr1

    SSC Veteran

    Points: 221

    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.

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    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

    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

    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.

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

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