Max real space in a varbinary(max) in SQL Server

Question

I am saving files (any type ) in a SQL table, using a varbinary(max), I find out that the max usage of this datatype is 8000, but what does the 8000 mean?

The online documentation says that is 8000 bytes. Does that mean that the maximum size of the file to be save there is 8000/1024 = 7.8125 KB?

I start testing and the maximum file that I can store is 29.9 MB. If I choose a larger file a get a SQLException.

String or binary data would be truncated. The statement has been terminated.

This statement is just **plain wrong** - the capacity of a `VARBINARY(MAX)` field is **2 GB** (2'147'483'647 bytes) - not just 8000...... — marc_s, Apr 20 '11 at 06:52

score 52 · Accepted Answer · edited Dec 13 '16 at 17:16

52

Implement SQL Server 2012 (codename Denali) when it's released - it has FileTable feature :)

varbinary(8000) is limited by 8000 bytes - that's for sure!
varbinary(max) is limited by 2 gigabytes
varbinary(max) FILESTREAM is limited by your file system (FAT32 - 2 Gb, NTFS - 16 exabytes)

edited Dec 13 '16 at 17:16

marc_s

732,580
175
1,330
1,459

answered Apr 19 '11 at 23:12

Eddy

781
5
4

1

I found the error ... based on your comments I realize that I had in my code a Archivo.Size = 8000 (Archivo is an SqlParameter), I thought that 8000 = Max ... !!!! , when I deleted this, it worked !! thanks a lot ... – carlos Apr 21 '11 at 16:02
useful answer ! – გენო მუმლაძე Aug 20 '19 at 13:28

score 10 · Answer 2 · edited Apr 19 '11 at 23:04

10

Taken from here:

http://msdn.microsoft.com/en-us/library/ms188362.aspx:

max indicates that the maximum storage size is 2³¹-1 bytes

which is 2 147 483 647 bytes. I'm not sure why it stops at 29.9MB.

edited Apr 19 '11 at 23:04

Arseni Mourzenko

50,338
35
112
199

answered Apr 19 '11 at 22:59

Mikecito

2,053
11
17

Beat you to it on that link but somehow didn't get the same reputation. ^^ Anyway, to clarify Mike's point, 2,147,483,647 bytes is 2GB, so excluding any timeout issues, that should be the maximum size of files before any truncation warnings. – Smudge202 Apr 19 '11 at 23:10
1

Yep, that's is exactly the same info I found, but for some reason it sends the exception exactly at 29.999 MB, I wasn't expecting to upload files of 2GB but niether expected 30 MB. Any other thougths of why am i getting this?, I alreadey changed the IIS parameters ... Thanks !! – carlos Apr 21 '11 at 15:57

score 5 · Answer 3 · answered Jun 12 '12 at 23:54

5

I got the "String or binary data would be truncated" error when trying to store 5MB using varbinary(max) on SQL Server 2005. Increasing the autogrowth size for the database solved the problem. Took me a while to figure out, so just thought I'd share :)

answered Jun 12 '12 at 23:54

annika

51
1
1

I am running into a similar issue: using SQL Server Express 10.0.0.2531 I cannot store a 2 MB file to the database. Column type is varbinary(max), auto growth is enabled, I even increased the starting size and now I have 800 MB free space in the DB. Still I get the truncation error. I am at a loss... – uceumern Jun 16 '16 at 14:19
@uceumern Did you find a solution? – aggsol Jun 19 '19 at 09:34

score 5 · Answer 4 · answered Apr 19 '11 at 22:57

What version of SQL Server are you using?

Varbinary on MSDN for SQL Server 2008 explicitly says that VarBinary(MAX) is for use when "the column data entries exceed 8,000 bytes."

Also, I would also take a look at the Filestream Capabilities in SQL Server 2008 if that is the server you are using.

Max real space in a varbinary(max) in SQL Server

4 Answers4

Linked