Archives of the TeradataForum

Message Posted: Wed, 30 Jun 2010 @ 09:25:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, June 29, 2010 02:33 -->

When one of our databases ran out of space, I found out that the database is using more space than allocated to it:

     LOCKING ROW FOR ACCESS
     select databasename,
     CAST(SUM((MaxPerm)/(1024*1024)) as DECIMAL(18,0) ) "Space Allocated (MB) ",
     CAST(SUM((CurrentPerm)/(1024*1024)) as DECIMAL(18,0) ) "Space Used (MB) ",
     CAST(SUM((MaxPerm-CurrentPerm)/(1024*1024)) as DECIMAL(18,0) ) "Space Available (MB) ",
     CAST((max(currentperm)*count(*))/(1024*1024) as DECIMAL(18,0) ) "Effective Size Used (MB)",
     CAST((sum(maxperm) - (max(currentperm)*count(*)))/(1024*1024) as
     DECIMAL(18,0) ) "Effective Available (MB)",
     sum(peakperm)/(1024*1024) "Peak space used (MB)",
     sum(maxperm)/(1024*1024) "Maximum Total Space (MB)", (max(currentperm)-
     average(currentperm))/
     case average(currentperm)
         when 0 then 1
         else average(currentperm)
     end "SkewRatio",
     ((max(currentperm)*count(*))-sum(currentperm))/(1024*1024) "SkewSize (MB)"
     from dbc.diskspace
     where databasename
     in
     (
     'databasename'
     )
     group by databasename;


                   Space           Space      Space           Effective Size  Effective       Peak space
     DatabaseName  Allocated (MB)  Used (MB)  Available (MB)  Used (MB)       Available (MB)  used (MB)

     Databasename  527             581       -54              763             -236            657.08

To make sure this:

     LOCKING ROW FOR ACCESS
     select  *
     from dbc.diskspace
     where databasename
     in
     (
     'databasename'
     ) and  currentperm>maxperm;


     Vproc  DatabaseName  AccountName  MaxPerm       MaxSpool  MaxTemp  CurrentPerm
     0      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     27497472.00
     1      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     26462720.00
     2      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     27527680.00
     3      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     26002432.00
     4      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     30755328.00
     5      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     28226048.00
     6      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     25856000.00
     7      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     26250752.00
     8      DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     24046080.00
     10     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     23380480.00
     12     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     21966848.00
     13     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     24307200.00
     14     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     27360768.00
     16     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     26019328.00
     17     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     24205312.00
     18     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     26359296.00
     19     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     24813056.00
     20     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     23615488.00
     21     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     22905344.00
     24     DATABASENAME  $HAB$&D&H    21247259.00   0.00      0.00     21977088.00

I know that DBC is allowed to go into negative space because of TJ. But strange that a normal is using more space than allocated. This has occurred couple of times before. Does any one know the reason for this?

Thanks in advace.

	<Prev	Next>		<<First	<Prev	Next>	Last>>