tempdb: filegroup is full while space is available both in filegroup & transaction log

  • admin 31599

    SSC-Addicted

    Points: 474

    SqlServer 2014 sp2cu2: having troubles with tempdb (350Gb full size: 8x25Gb primary group + 150 Gb log).
    Error is Msg 1105, Level 17, State 2 Could not allocate space for object 'dbo.SORT temporary run storage: ..422202655866..' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space etc..

    db properties: Size 350000 Mb, 199978 Mb Space Available.
    Seems that it is transaction log issue, right? But DBCC SQLPERF(LOGSPACE) shows 4% use for tempdb.

    Did not have this on sp2 initial version (without any CU)..
    Will patch with cu5 waiting for fix from cu3 (Unexpected growth of tempdb data files when using SQL Server Service Broker) but not sure if that is the reason.

    I need some ideas to check. Please advise.
    Restarting instance helps for a while but that's not the way.

  • Perry Whittle

    SSC Guru

    Points: 233859

    at the time your query runs it is running out of tempdb space, likely because other users have space allocated for their queries too.
    Restarting works because you sever all connections, until of course other users start connecting again.

    Increase tempdb size\space, more than that, find out why it's using so much and trim it down

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    I've seen 'dbo.SORT' being full specifically when you have set to sort an index rebuild to SORT_IN_TEMPDB.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Sue_H

    SSC Guru

    Points: 90673

    And it's telling you the primary filegroup is full so you would want to look at the data files not the log file. The size, space needed, growth settings, etc.

    Sue

  • admin 31599

    SSC-Addicted

    Points: 474

    Perry Whittle - Thursday, May 18, 2017 8:33 AM

    at the time your query runs it is running out of tempdb space, likely because other users have space allocated for their queries too.
    Restarting works because you sever all connections, until of course other users start connecting again.

    Increase tempdb size\space, more than that, find out why it's using so much and trim it down

    only one query run at a time when problem appear, load is very small ~200k rows to be inserted ot smth.
    it happens on random query just when space is not enough
    anyway, why DBCC SQLPERF(LOGSPACE) shows very small usage of tempdb? isn't that strange?

  • admin 31599

    SSC-Addicted

    Points: 474

    Sue_H - Thursday, May 18, 2017 8:40 AM

    And it's telling you the primary filegroup is full so you would want to look at the data files not the log file. The size, space needed, growth settings, etc. 

    Sue

    agree, but files are mostly not in use and filled max by 10% (any of all 8 in the filegroup)
    I did not mention that there is AlwaysOn availability group set up

  • admin 31599

    SSC-Addicted

    Points: 474

    Henrico Bekker - Thursday, May 18, 2017 8:35 AM

    I've seen 'dbo.SORT' being full specifically when you have set to sort an index rebuild to SORT_IN_TEMPDB.

    there is no activity with creating or altering/rebuilding indexes..
    ah, that's because of target table insert index is refreshed
    so if target table heap is ~21.5 Gb plus 5 Gb index and server is trying to sort it is not enough 25 Gb per tempdb file!

    super idea!! checking that

  • Perry Whittle

    SSC Guru

    Points: 233859

    admin 31599 - Thursday, May 18, 2017 8:42 AM

    Perry Whittle - Thursday, May 18, 2017 8:33 AM

    at the time your query runs it is running out of tempdb space, likely because other users have space allocated for their queries too.
    Restarting works because you sever all connections, until of course other users start connecting again.

    Increase tempdb size\space, more than that, find out why it's using so much and trim it down

    only one query run at a time when problem appear, load is very small ~200k rows to be inserted ot smth.
    it happens on random query just when space is not enough
    anyway, why DBCC SQLPERF(LOGSPACE) shows very small usage of tempdb? isn't that strange?

    Thats showing used logspace, not datafile space, use this query next time to see file freespace on the tempdb

    SELECT df.name AS LogicalFileName

    , ISNULL(fg.name, 'Log') AS FilegroupName

    , physical_name AS PhysicalOSName

    , CAST((df.size / 128) AS DECIMAL(18,2)) AS SizeMBs

    , CAST((FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS DECIMAL(18,2)) AS SpaceUsedMBs

    , CASE df.growth

    WHEN 0 THEN 'No growth'

    ELSE 'Growth allowed'

    END AS GrowthAllowed

    , CASE

    WHEN df.max_size / 128 = 0 THEN CAST(df.size / 128 as varchar(50)) + ' MBs'

    WHEN df.max_size / 128 = 2097152 and df.growth = 0 THEN 'No growth'

    WHEN df.max_size = 268435456 THEN '2TB'

    ELSE CAST(df.max_size / 128 AS VARCHAR(10)) + ' MBs'

    END AS MaxGrowthSize

    , CASE df.is_percent_growth

    WHEN 0 THEN CAST(df.growth / 128 AS VARCHAR(10)) + ' MBs'

    ELSE CAST(df.growth AS VARCHAR(10)) + ' %'

    END AS Growth

    , (df.size / 128) - (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS FreeMBs

    FROM sys.database_files df LEFT OUTER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id

    ORDER BY df.type

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • admin 31599

    SSC-Addicted

    Points: 474

    Perry Whittle - Friday, May 19, 2017 4:12 AM

    Thats showing used logspace, not datafile space, use this query next time to see file freespace on the tempdb

    Thanks a million! Found that all the files in tempdb fg are filled by one INSERT query. Investigating that.
    Can't predict the reason especially several issues fixed in different CUs from Microsoft referencing poor plans problems in AlwaysOn environment.

  • admin 31599

    SSC-Addicted

    Points: 474

    Resolved after optimizing sp code.
    Thank you!

  • This was removed by the editor as SPAM

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

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