[firebird-support] Re: Firebird FDB-filesize too Big

[AngelBlaZe]

Database statistics do not count blob type fields towards the size. Since blobs 
are used for binary things like images and documents often they are the largest 
users of space.

Here is some sql to find the blobs and their sizes. (original from 
http://stackoverflow.com/questions/3699921/how-can-i-measure-the-amount-of-space-taken-by-blobs-on-a-firebird-2-1-database)

Blob total size
EXECUTE BLOCK RETURNS (BLOB_SIZE BIGINT)
AS
  DECLARE VARIABLE RN CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE FN CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE S BIGINT;
BEGIN
  BLOB_SIZE = 0;
  FOR
    SELECT r.rdb$relation_name, r.rdb$field_name 
      FROM rdb$relation_fields r JOIN rdb$fields f 
        ON r.rdb$field_source = f.rdb$field_name
    WHERE f.rdb$field_type = 261 and (r.rdb$relation_name not starts with 
'RDB$' AND r.rdb$relation_name not starts with 'MON$' )
    INTO :RN, :FN
  DO BEGIN
    EXECUTE STATEMENT
      'SELECT SUM(OCTET_LENGTH(' || :FN || ')) FROM ' || :RN ||
      ' WHERE NOT ' || :FN || ' IS NULL'
    INTO :S;
    BLOB_SIZE = :BLOB_SIZE + COALESCE(:S, 0);
  END
  SUSPEND;
END

BLOB Size per column,table
EXECUTE BLOCK RETURNS (BLOB_SIZE BIGINT,tbl varchar(255),colm 
varchar(255),csize varchar(255))
AS
  DECLARE VARIABLE RN CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE FN CHAR(31) CHARACTER SET UNICODE_FSS;
  DECLARE VARIABLE S BIGINT;
BEGIN
  BLOB_SIZE = 0;
  FOR
    SELECT r.rdb$relation_name, r.rdb$field_name 
      FROM rdb$relation_fields r JOIN rdb$fields f 
        ON r.rdb$field_source = f.rdb$field_name
    WHERE f.rdb$field_type = 261 and (r.rdb$relation_name not starts with 
'RDB$' AND r.rdb$relation_name not starts with 'MON$' )
    INTO :RN, :FN        
  DO BEGIN
    EXECUTE STATEMENT
      'SELECT SUM(OCTET_LENGTH(' || :FN || ')) FROM ' || :RN ||
      ' WHERE NOT ' || :FN || ' IS NULL'
    INTO :S;
    BLOB_SIZE = COALESCE(:S, 0);
    tbl = :RN;     
    colm = :FN;    
    csize = (BLOB_SIZE  / 1024.0 / 1024.0) || ' Megabytes';
    SUSPEND;
  END        
END


--- In firebird-support@yahoogroups.com, "oleri@..." <oleri@...> wrote:
>
> Hallo there,
> 
> i have a strange problem, i do not know how to solve ist.
> 
> Description:
> 
> The Database filesize on disk is about 18 GB. Backup/Restore do NOT lower the 
> filezise. The Database statistics says the sum(Table) Filesize is about 3,7 
> GB. Where does the difference comes?!
> 
> I think i located the table where does the problems come.
> I have made a restore and similar take a look to the filesize of the Database 
> during this restore. There is one table called "Maila" where the database 
> filesize "explodes" during write (about 12 GB of 360000 datasets).
> 
> If i make a metadata extraction of this database, and a script exctraction of 
> this table,write this script into the empty database, then its only 1 GB 
> Filezise.
> 
> Who can explain me what happend here? Because 18 GB ist about 5 times too big!
> 
> 
> thanks in advance.
> Hans
>