May 18, 2017 at 8:00 am
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.
May 18, 2017 at 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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2017 at 8:35 am
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
May 18, 2017 at 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
May 18, 2017 at 8:42 am
Perry Whittle - Thursday, May 18, 2017 8:33 AMat 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?
May 18, 2017 at 8:45 am
Sue_H - Thursday, May 18, 2017 8:40 AMAnd 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
May 18, 2017 at 9:21 am
Henrico Bekker - Thursday, May 18, 2017 8:35 AMI'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
May 19, 2017 at 4:12 am
admin 31599 - Thursday, May 18, 2017 8:42 AMPerry Whittle - Thursday, May 18, 2017 8:33 AMat 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" 😉
May 20, 2017 at 9:41 pm
Perry Whittle - Friday, May 19, 2017 4:12 AMThats 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.
May 21, 2017 at 12:42 am
Resolved after optimizing sp code.
Thank you!
May 21, 2017 at 10:46 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply