Loading ...
Sorry, an error occurred while loading the content.

Firebird FDB-filesize too Big

Expand Messages
  • oleri@rocketmail.com
    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
    Message 1 of 2 , Apr 26 5:52 AM
    View Source
    • 0 Attachment
      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
    • 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
      Message 2 of 2 , Apr 26 12:50 PM
      View Source
      • 0 Attachment
        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
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.