Database file size problem

To shrink the database just back it up then restore it replacing the existing database.

Firebird will re-use the space that has become available in the file.

The reason that your file doesn't "shrink", is that files simply cannot shrink, they can only become larger. Whenever some application creates a smaller file, it will re-create the file and remove the old file.

Obviously, for a database, this doesn't make sense. You don't want to delete the database file and re-create a new one, just to free up some disk space. Imagine Firebird doing this every now and then on huge files like 40 or 100 Gb. Instead, it will re-use empty space inside the file.

All in all: nothing to worry about.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

1- I create sample database file (with ibexpert)
2- I create table in the database
3- I create ten field in the table
4- now database size ~3mb
5- I adding 100000 record in the table with VB6 and ODBC
6- now database size is 20 mb
7-I delete this 100000 record with VB6 and ODBC
8-now database size is steel 20mb
9- why database size is not 2 mb ?

sory bad english language

Read the message I posted earlier, it explains why this happens.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

I have read through the previous response.

My problem is similar, we are using a firebird database (new V2 release SuperServer on Fedora Core 4) to store images that are being sampled from a camera. There are about 100 images (each image is 50 ~ 70KB) inserted per second. Obviously we know this will keep the database expanding, so we have created an application that runs on the database server which removes the old images. The app is designed to keep a certain amount of images within the database...say at 50,000 images.

The problem we are having is that the database keeps expanding. it is not constant, but it is definitely increasing. It started at about 4.6 GB, remained there for about 2 hours, then increased to 9.6GB... this stayed constant overnight for about 10 hours. Today the size increased to 11GB and then 33GB in 1.5 hours.

We understand the database will not decrease in size, and checking with the app on the server there have never been more than 60,000 images at one time in the DB. So why would the file keep on expanding?

This is a very important project and it is imperative that we solve this problem.

Hmm, it could be that blobs are a bit different.

First of all, make sure all your transactions are properly committed, that goes for "read" transactions as well. If an old read-transaction isn't committed, Firebird will think it still needs access to that data, despite the fact that a new transaction will show there's only 50,000 images.

You can check your transaction counters by using "gstat" to check the database statistics. It's on the header page info.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

I will check using gstat, from what i know we are definitely commiting the transaction. If there is a problem accessing the database, then the transaction will be rolled back.

I assume if there is an interrupted connection to the database that the database itself will roll back?

We either commit or rollback, we never commit-retaining. We are using Borland C++ 2006 with the ibsql components. we call the commit when done with the access.

That could explain why the database does expand. If we disconnected from the database, would that free up those old transactions?

oh, when you mention that blobs could be a bit different, do you mean that they are handled differently in the database... ie maybe garbage collection is not working correctly?

Yes.


You really should commit after each database action you do. eg, select or update or whatever.

If an application has a read on data (based on transaction isolation), and the application would stay open, this would mean it doesn't allow the transaction counter to move forward.


--
Martijn Tonies
Upscene Productions

We worked the read stuff out more recently. To keep data integrity we will do a batch of command within one transaction ... a couple of selects and then a write... afterwards we will commit the transaction.

Is this bad practice which results in our error? Should we be doing each one in a separate transaction?

Does closing the connection disconnect all those other transactions that may be left open. This way would it not be better to connect and disconnect from the database after each transaction thus ensuring that each transaction is closed .. read or otherwise.

Sounds OK to me.

Have you checked your gstat output yet?