Programming Forums - View Single Post - Storing BLOBs in a database

jonyzz · Jan 17th, 2007, 8:42 AM

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.

Jan 17th, 2007, 8:42 AM	#1
jonyzz Programmer Join Date: Aug 2005 Location: null Posts: 40 Rep Power: 0	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.