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;