Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ).
But now we have a database, that is storing Images on a Image field.
the problem is, the database is HUGE ( it has now 500GB MDF, and 700GB on LDF file )and every log backup has more than 70GB !
What is the best way to store Images on a database? I've seen a lot of posts saying about store the Physical patch of the image on a VARCHAR field. But this way, we need to store the images in some place, right? It will fill the storage in the same way. Or am I wrong?
We have full backups every day at 22:00 and log backups every hour.
This is the sizes of the log backups.
EDIT1:
Each "Image" has a binary code, and it has 43679 characters ).
1 millions rows for this table ( 1.141.947 images ( with coduser, image, and etc )
EDIT2:
We are now using varbinary(max), but it's not solving our problems. It has now almost 700mb after 20 minutes.
Best Answer
If you have a high volume of changes (inserts, updates or deletes) to those image BLOBs, then you would be best served to NOT store them within the database in the long run. Assuming your not having performance issues, which some features like FileStreaming and FileTables can help with, your problem is going to be the "velocity" of change to those BLOBs of data, which will manifest a single byte change within the BLOB into a full recreation of the entire BLOB in the transaction.
One possible "work-around" if you MUST keep them in the database AND you can tolerate some data lose of the binaries is instead store them in a separate database on the same instance. Set that database's recovery model to SIMPLE, and just do some extra differential or multi-day full backups. Then to query the data, utilize cross-database joins and/or views that do the same. Not an ideal situation, but at least a way to minimize data lose if your recovery needs can vary by individual data item (as opposed to the entire database).