Firebird DB - verkleinern ohne Backup/Restore

[Gruber_Hans_12345]

Hmmm bei mir ist "oldest snapshot" und 2oldest transaction" fast immer gleich .... ?

Also in der DB werden immer nur datensätze per Insert eingefügt
also transaktion aufmachen INSERT Transaktion zu (das dauert also immer nur ganz ganz kurz)
das sagen wir mal so 5000-20000 pro Tag
und am ende des tages werden dann wieder so 4000-18000 Datensätze (alte) gelöscht

also bisschen darf die DB immer wachsen aber eben nicht so enorm, da läuft was schief ....

so das ist gstat -r -i

[edit] und falls man es nicht sieht, es geht hier NUR um die Tabelle LOG_ITEM alle anderen sind beinahe leer[/edit]

Code:

Database "d:\interbase\system.fdb"
Database header page information:
   Flags         0
   Checksum      12345
   Generation      71397630
   Page size      8192
   ODS version      11.2
   Oldest transaction   71396618
   Oldest active      71396619
   Oldest snapshot      71396619
   Next transaction   71396620
   Bumped transaction   1
   Sequence number      0
   Next attachment ID   998
   Implementation ID   26
   Shadow count      0
   Page buffers      4000
   Next header page   0
   Database dialect   3
   Creation date      Dec 4, 2011 16:35:43
   Attributes      force write

    Variable header data:
   Sweep interval:      20000
   *END*


Database file sequence:
File d:\interbase\system.fdb is the only file

Analyzing database pages ...
CLIENT_LOG (135)
    Primary pointer page: 164, Index root page: 165
    Average record length: 63.64, total records: 16180
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 207, data page slots: 207, average fill: 77%
    Fill distribution:
    0 - 19% = 0
   20 - 39% = 1
   40 - 59% = 0
   60 - 79% = 206
   80 - 99% = 0

    Index RDB$PRIMARY7 (0)
   Depth: 2, leaf buckets: 10, nodes: 16180
   Average data length: 1.00, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 0
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 10

LOG_ITEM (128)
    Primary pointer page: 150, Index root page: 151
    Average record length: 97.36, total records: 523953
    Average version length: 161.97, total versions: 37, max versions: 1
    Data pages: 11451, data page slots: 165710, average fill: 67%
    Fill distribution:
    0 - 19% = 2235
   20 - 39% = 160
   40 - 59% = 86
   60 - 79% = 4394
   80 - 99% = 4576

    Index LOG_ITEM_DATUM (2)
   Depth: 3, leaf buckets: 558, nodes: 523953
   Average data length: 2.09, total dup: 12398, max dup: 1
   Fill distribution:
        0 - 19% = 2
       20 - 39% = 12
       40 - 59% = 22
       60 - 79% = 38
       80 - 99% = 484

    Index LOG_ITEM_MODUL (1)
   Depth: 3, leaf buckets: 553, nodes: 523953
   Average data length: 0.00, total dup: 523839, max dup: 268780
   Fill distribution:
        0 - 19% = 0
       20 - 39% = 13
       40 - 59% = 443
       60 - 79% = 10
       80 - 99% = 87

    Index RDB$PRIMARY1 (0)
   Depth: 3, leaf buckets: 432, nodes: 523953
   Average data length: 1.09, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 3
       20 - 39% = 18
       40 - 59% = 15
       60 - 79% = 28
       80 - 99% = 368

LOG_MODUL (129)
    Primary pointer page: 152, Index root page: 153
    Average record length: 42.85, total records: 486
    Average version length: 9.00, total versions: 485, max versions: 1
    Data pages: 8, data page slots: 22, average fill: 67%
    Fill distribution:
    0 - 19% = 2
   20 - 39% = 1
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 5

LOG_PRIORITY (130)
    Primary pointer page: 154, Index root page: 155
    Average record length: 0.00, total records: 0
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 0, data page slots: 0, average fill: 0%
    Fill distribution:
    0 - 19% = 0
   20 - 39% = 0
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 0

    Index RDB$PRIMARY2 (0)
   Depth: 1, leaf buckets: 1, nodes: 0
   Average data length: 0.00, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 1
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 0

LOG_PRUNE (132)
    Primary pointer page: 158, Index root page: 159
    Average record length: 38.54, total records: 13
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1, data page slots: 1, average fill: 9%
    Fill distribution:
    0 - 19% = 1
   20 - 39% = 0
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 0

    Index RDB$PRIMARY3 (0)
   Depth: 1, leaf buckets: 1, nodes: 13
   Average data length: 1.08, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 1
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 0

LOG_SEND_SUPPORT (133)
    Primary pointer page: 160, Index root page: 161
    Average record length: 30.00, total records: 5
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1, data page slots: 1, average fill: 3%
    Fill distribution:
    0 - 19% = 1
   20 - 39% = 0
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 0

    Index RDB$PRIMARY5 (0)
   Depth: 1, leaf buckets: 1, nodes: 5
   Average data length: 1.20, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 1
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 0

LOG_SETTINGS (131)
    Primary pointer page: 156, Index root page: 157
    Average record length: 44.33, total records: 3
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1, data page slots: 1, average fill: 2%
    Fill distribution:
    0 - 19% = 1
   20 - 39% = 0
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 0

    Index RDB$PRIMARY4 (0)
   Depth: 1, leaf buckets: 1, nodes: 3
   Average data length: 7.67, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 1
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 0

SETTINGS (134)
    Primary pointer page: 162, Index root page: 163
    Average record length: 45.80, total records: 5
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1, data page slots: 1, average fill: 4%
    Fill distribution:
    0 - 19% = 1
   20 - 39% = 0
   40 - 59% = 0
   60 - 79% = 0
   80 - 99% = 0

    Index RDB$PRIMARY6 (0)
   Depth: 1, leaf buckets: 1, nodes: 5
   Average data length: 10.00, total dup: 0, max dup: 0
   Fill distribution:
        0 - 19% = 1
       20 - 39% = 0
       40 - 59% = 0
       60 - 79% = 0
       80 - 99% = 0