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;

  

DatabaseName

maxPerm_GB

Currentperm_GB

Peakperm_GB

Freeperm_GB

Percent_Used

PRGROCERY

243

91

103

152

37

   

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;

Vproc

Currentperm_GB

MaxPerm_GB

0

2

3

1

1

3

2

1

3

3

1

3

4

1

3

5

1

3

6

2

3

7

1

3

8

1

3

9

2

3

10

1

3

11

1

3

12

2

3

13

1

3

14

1

3

15

1

3

16

1

3

17

2

3

18

1

3

19

1

3

20

1

3

21

1

3

22

1

3

23

1

3

24

1

3

25

1

3

26

2

3

27

1

3

28

3

3

29

1

3

30

1

3

31

1

3

32

1

3

33

1

3

34

1

3

35

2

3

36

1

3

37

1

3

38

1

3

39

1

3

40

1

3

41

2

3

42

1

3

43

1

3

44

1

3

45

1

3

46

1

3

47

2

3

48

2

3

49

1

3

50

2

3

51

1

3

52

1

3

53

2

3

54

1

3

55

2

3

56

1

3

57

1

3

58

1

3

59

2

3

60

1

3

61

1

3

62

1

3

63

2

3

64

1

3

65

1

3

66

2

3

67

1

3

68

1

3

69

1

3

70

1

3

71

1

3

72

1

3

73

1

3

74

1

3

75

1

3

76

1

3

77

2

3

78

1

3

79

1

3

 

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;

 

TableName

ActualSpaceGB

EffectiveSpaceGb

WasteSpaceGB

WasteSpace%

SkewFactor

SELL_STATUS                                             

26

164

138

84

84

SALES_HIST                                     

31

38

7

20

20

STOCK

0

1

1

98

98

STOCK_HIST

0

0

0

0

0

INVENTORY

0

0

0

0

0

…….

……

…….

……

…….

…….

……..

……

……

……

……

……


Comments