Storing BLOBs in a database - problem
 

Hi,
I have an interesting problem and I was wondering if any of you guys have an idea about solving it.
I am using MS SQL Server 2005 to write a project that stores images in a database. I use the "image" type for storing them in a table. When creating my database I set the database files growth limit to 10 MB (the .mdf and the .log file). But what I noticed is that after 4 days of using the database (inserting and deleting images) the databse .log file was 4 GB and the .mdf file was 2 GB large. I also set up "Auto shrink on delete" when creating the database, but it obviously didn't help. I got the message "Not enough space on hard disk." and it was because of the unlimited growth of my database. I worked on the same project a month ago - with a different database, and I hadn't set the growth limits, nor the "Auto shrink on delete" option. And I got the same problem then. I supposed that making a new database with new options would help, but I was wrong...
Does this problem have anything to do with storing the BLOBs in the row itself, instead of storing only a pointer to their location?
I am writing about MS SQL Server, because I haven't worked on a similar project before. But I suppose that there is a common idea for working with BLOBs.
Do you have any ideas? Has any of you worked with BLOBs? How do you sore them?
I forgot to mention that the images I inserted in my table were not bigger than 1,5 Mb.
I also used transactions in the second database, but not in the first one.
Thank you in advance, guys!
Best regards.

Out of interest, why are you going for the BLOB approach rather than storing the images in a directory and recording their path in the database?

Well, it is required in my assignment. The images must be stored in the database. I thought that this approach is often used, but maybe I was wrong.
So how about download file servers - do they store only the files paths in their database?
Is this approach used anywhere at all?

Well, if it says that the images have to be stored in the database, then I guess you have no option.

However, most systems I've encountered store files in the filesystem, often in a single directory, with the ID or GUID of the database row corresponding to the filename.

However, I'm uncertain what advantages this might have (perhaps faster file access?), and any advantages this approach has in other databases may not be relevant with MS SQL Server.

One of my db projects that included images i was advised by my teacher to store my pictures in a directory and anly put their address in the db. But i have seen that there is a book about multimedia databases. Title :
" Distributed Multimedia Database Technologies Supported by MPEG-7 and MPEG-21". I dont know if it will help.

I, too, think storing a reference to the data (ie, file name) is a better solution. Storing the raw data in the database makes it harder to change the data. Say you've got a bunch of thumbnail images, each 100x100, and the project requirements change, and thumbnails must now be 50x50. Using many paint programs or picture viewers, you can do a batch conversion of all the files. If the data is in a database, you can't do that; you'd need to write a program at least for the database access portion (though after the chunks of data were retrieved, and saved as files, you could use the above solution).

If it's a requirement for, say, an assignment, perhaps you should speak to your instructor. Also, the 'store the images in the database' is a little ambiguous, because storing a link to them might fit this requirement. Again, speak to your instructor, express your concerns, and if the requirement is actually to store the binary data in the database, you might ask why the requirement is formulated this way.

An excellent requirement, in some instances. ID photos come to mind.

Good point. That hadn't occurred to me. I guess I was thinking too much in terms of specifics, and inferred from the OP's course title that the objects would be of a more dynamic nature (not to mention large).

Then waht is the point of having an image or varbinary(MAX) database types? What are they used for? Are they used at all? :D