Failure 2644: No more room in database <DataaseName> while there is space available

Check Database Space:

SELECT DatabaseName,

CAST(SUM(MaxPerm)/1024/1024/1024 as DECIMAL (6,0)) as MaxPerm_GB,

CAST(SUM(CurrentPerm)/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,

CAST(SUM(PeakPerm)/1024/1024/1024 as DECIMAL (6,0)) Peakperm_GB,

CAST((SUM(MaxPerm)/1024/1024/1024 -SUM(Currentperm)/1024/1024/1024) as DECIMAL (6,0)) as Freeperm_GB,

CAST(((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) as DECIMAL (3,0)) (FORMAT 'zz9.99%', TITLE 'Percent Used')

FROM DBC.DiskSpaceV

where DatabaseName='PRGROCERY'

GROUP BY 1

ORDER BY 4 DESC;

We can see there is lots of space available in the database lets check the database space per amp.

Check Database Space per AMP:

select VProc,

CAST(CurrentPerm/1024/1024/1024 as DECIMAL (6,0)) Currentperm_GB,

CAST(MaxPerm/1024/1024/1024 as DECIMAL (6,0)) as MaxPerm_GB

from dbc.DiskSpace where DatabaseName='PRGROCERY'

order by 1;

Space is managed on a "per-AMP" basis. If one AMP is out of space, the database is full. We can see AMP 28 is full therefore we are getting Failure 2644: No more room in database. Cause of this is Skew Factor table that can take more space than its size.

Check Actual space, WastedSpace and SkewFactor of all tables in PRGROCERY database :

SELECT DatabaseName, TableName

,SUM(CurrentPerm) /1024**3 AS ActualSpaceGB

,MAX(CurrentPerm)*(HASHAMP()+1) /1024**3 as EffectiveSpaceGB

,EffectiveSpaceGB-ActualSpaceGB AS WasteSpaceGB

,100*((EffectiveSpaceGB-ActualSpaceGB)/ EffectiveSpaceGB) AS "WasteSpace%"

,(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor

FROM DBC.TableSize

WHERE DatabaseName='PRGROCERY'

GROUP BY 1,2

ORDER BY 5 DESC;