[firebird-support] Database Size Is Not Reducing

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

Dear All,
I am using Firebird 2.5 database.
I have a table that has column of type Blob field, this table also contains 
some other columns.
The issue is, I had total 78,000 records in this table. What I did is as every 
record was containing image in it, I then ran an update query and set Null 
value to all the records for this blob column.
I then backed up the database using GBack and then restored. After restored the 
database there is no change in database size.
Any idea why it is so ?
I was referring to below link, 

http://www.devrace.com/en/fibplus/articles/2161.php

which says:It is important to know and remember that in contrast to other 
fields, BLOBs data are not stored in the table record. Table records store only 
BLOB_ID, whereas BLOB body is kept in separate database tables.
Is because of this reason there is no change in database size?
If it so then, I am curious to know where does Firebird store the images?

Thanks In Advance.
With Best Regards.
Vishal

Re: [firebird-support] Database Size Is Not Reducing

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Apr 6, 2015, at 9:08 AM, Vishal Tiwari vishuals...@yahoo.co.in 
> [firebird-support]  wrote:
> 
> I am using Firebird 2.5 database.

what operating system?

Cheers,

Ann

Re: [firebird-support] Database Size Is Not Reducing

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Apr 6, 2015, at 9:08 AM, Vishal Tiwari vishuals...@yahoo.co.in 
> [firebird-support]  wrote:
> 
> I am using Firebird 2.5 database.  I have a table that has column of type 
> Blob field, this table also contains some other columns.
> 
> The issue is, I had total 78,000 records in this table. What I did is as 
> every record was containing image in it, I then ran an update query and set 
> Null value to all the records for this blob column.
> 
> I then backed up the database using GBack and then restored. After restored 
> the database there is no change in database size.   Any idea why it is so ?

No.  I assume you committed the transaction that set the blobs to null before 
you backed up the database and that the image blobs were of considerable size 
(more than 100 bytes or so).  How big was the original database?  How big is 
the recreated database?  Did you run gstat to see where the space is going?  

Just deleting data, without the gbak backup/restore will not reduce the size of 
the database.  Firebird's internal storage is complex - much more so than 
databases which store each table in its own file. Releasing space from the 
middle of a file is not possible. 
> 
> I was referring to below link,  
> http://www.devrace.com/en/fibplus/articles/2161.php
> which says: 
> It is important to know and remember that in contrast to other fields, BLOBs 
> data are not stored in the table record. Table records store only BLOB_ID, 
> whereas BLOB body is kept in separate database tables.

That's not entirely correct.  If the blob data fits on the same data page as 
the record, it will be stored there.  If not, it will be stored on an overflow 
page.  Gstat doesn't report overflow pages - whether for large blobs or records 
larger than a page.  Or rather it didn't when last I looked.  Someone may have 
fixed that.  Even if gstat doesn't report them, overflow pages belong to a 
table.  They are located through records in table.  Their page headers include 
information that identifies the table they belong to, the record or blob they 
contain, and their sequence in that record or blob.
> 
> Is because of this reason there is no change in database size?

No.
> 
> If it so then, I am curious to know where does Firebird store the images?

On database pages - as above.

Good luck,

Ann

RE: [firebird-support] Database Size Is Not Reducing

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

On Apr 6, 2015, at 9:08 AM, Vishal Tiwari 
vishuals...@yahoo.co.in [firebird-support] 
mailto:firebird-support@yahoogroups.com>> 
wrote:

I am using Firebird 2.5 database.

what operating system?

 Also, what page size?  How big (on average) was the size of the BLOBs?

Re: [firebird-support] Database Size Is Not Reducing

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

Operating system I am using is Windows 7
With Best Regards.
Vishal

Re: [firebird-support] Database Size Is Not Reducing

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

Then what could be the best way to reduce the database size?
All suggestions are appreciated.

With Best Regards.
Vishal

Re: [firebird-support] Database Size Is Not Reducing

[marcus mar...@antiphasis.net [firebird-support]]

On 07.04.2015 08:16, Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support] wrote:
>  
> 
> Then what could be the best way to reduce the database size?
> 
> All suggestions are appreciated.
Hej Vishal

played around a little bit, unable to reproduce your problem.
Even after setting some blobfields to NULL, leaving isql or flamerobin
open and the update statement uncommitted, gbak -b with -l or -g or -l
and -g : the backup files are all distinctly smaller than the backup
with all blob content. Same for all the restored databases.

What i've done:
- System Win7 64, FB 2.5.2
- created a new database, new table t1 (id integer, blobfield blob
subtype 0), all operations in the same directory
- create first backup for empty db
- insert 10 ids and random blob data
- create second backup
- update t1 set blob=null where id > 2
  - leave either flamerobin or isql statement uncommited
  - backup db to different names: both smaller than second backup
  - playing with backup options, backup to different names: still
smaller than than the second backup
- commit statement
  - backup db to different names: both smaller than second backup
  - playing with backup options, backup to different names: still
smaller than than the second backup
- restore all the backups to according names: size equals between all
backups where i've done the update...set...null, all smaller than the
original database with all 10 blobs.
- opened all databases: blobs 3..10 are NULL

So, question is: how do you do your backup/restore? cmd, service api,
some 3rd Party components?
Doublechecked file locations? Stumpled upon that to often for myself.

hth, Marcus



> 
> 
> With Best Regards.
> 
> Vishal
> 
> 


[Non-text portions of this message have been removed]