Firebird DB - verkleinern ohne Backup/Restore

[Gruber_Hans_12345]

nein ich verwende weder Trigger noch Stored Procedure

Ich habe nun mal ein komplettes Backup und Restore durchgeführt nun ist die DB mit den 550.000 Records gerade mal 1.24GB groß

ich werde mal beobachten wie und was sich tut die nächsten tage

nun schaut das gstat auf alle fälle mal so aus

Code:

Database "d:\interbase\system.fdb"
Database header page information:
   Flags         0
   Checksum      12345
   Generation      41
   Page size      8192
   ODS version      11.2
   Oldest transaction   29
   Oldest active      30
   Oldest snapshot      30
   Next transaction   31
   Bumped transaction   1
   Sequence number      0
   Next attachment ID   3
   Implementation ID   26
   Shadow count      0
   Page buffers      4000
   Next header page   0
   Database dialect   3
   Creation date      Jan 11, 2013 15:28:32
   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: 16189
    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: 16189
   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: 99.59, total records: 563616
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 10082, data page slots: 10082, average fill: 83%
    Fill distribution:
    0 - 19% = 2
   20 - 39% = 6
   40 - 59% = 8
   60 - 79% = 4354
   80 - 99% = 5712

    Index LOG_ITEM_DATUM (2)
   Depth: 2, leaf buckets: 555, nodes: 563616
   Average data length: 2.08, total dup: 12801, max dup: 1
   Fill distribution:
        0 - 19% = 0
       20 - 39% = 0
       40 - 59% = 1
       60 - 79% = 0
       80 - 99% = 554

    Index LOG_ITEM_MODUL (1)
   Depth: 2, leaf buckets: 345, nodes: 563616
   Average data length: 0.00, total dup: 563502, max dup: 268784
   Fill distribution:
        0 - 19% = 0
       20 - 39% = 0
       40 - 59% = 4
       60 - 79% = 0
       80 - 99% = 341

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

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

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

[RWarnecke]

Was passiert, wenn gar kein Sweep Interval gesetzt ist ? Das habe ich noch nicht so richtig verstanden. Ich habe es zumindest soweit verstanden, dass der Sweep Interval irgendwas mit dem endgültigen Löschen der Datensätze zu tun hat.

[mkinzler]

Ein (Hintergrund-)Sweep löscht eigentlich nicht, sondern gibt nur nicht mehr benötigte Versionen von Datensätze frei, welche dann bei einem manuellen Sweep ( z.B. Backup/Restore Zyklen) entfernt werden oder deren Platz von anderen Datensatzversionen eingenommen werden kann.