Mdf file growing too large inserting binary data

Question

I'm taking jpegs and inserting them into a table as a byte array in a varbinary(MAX) data type field. However, the mdf file is growing three to four times larger in size the the total size of all of the files I'm inserting. I'm using a standard c# coding technique to take a webresponse and convert it into a memorystream:

byte[] result;
byte[] buffer = new byte[4096];

using (Stream responseStream = request.GetResponse().GetResponseStream())
{
    using (MemoryStream memoryStream = new MemoryStream())
    {
        int count = 0;
        do
        {
            count = responseStream.Read(buffer, 0, buffer.Length);
            memoryStream.Write(buffer, 0, count);

        } while (count != 0);

        result = memoryStream.ToArray();

    }
}

And yet somehow 512mb of jpegs ends up growing the mdf over 2gb in size. Where I do the insert into the table I am defining the length on this field as well using result.length. Auto grow is set to 5%.

Answer 1

A 512 MB jpeg should take just a bit over 512 mb in the database. The mdf itself may grow bigger, but that is subject to the database configured growth increment. sp_spaceused should show the used vs. free space.

But the way you're processing the file is actually quite bad, using a memory stream. Is never going to work. Read Download and Upload images from SQL Server via ASP.Net MVC for the correct way of streaming files from HTTP into the database. You must use UPDATE blob.write because of the app process memory size constraints and also because of database minimally logged operations requirements. The article linked explains in more details why and how to do it. you can use the same technique also to stream in a web response, w/o creating a full copy in memory.