7

I have an entity with a binary datatype and a corresponding varbinary(max) column in SQL Server. EF creates this:

CREATE TABLE [dbo].[Attachments] 
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [FileName] NVARCHAR(255) NOT NULL,
    [Attachment] VARBINARY(MAX) NOT NULL
);

When I try to call .SaveChanges() from Entity Framework, I get an error:

Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060

I understand the error, there's plenty of that on Google but I don't understand why I'm getting it. Shouldn't this be managed by Entity Framework / SQL Server?

Richard

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard
  • 5,810
  • 6
  • 29
  • 36

1 Answers1

19

The only way I can see you getting this error with that table definition is if you have previously had a large fixed width column that has since been dropped.

CREATE TABLE [dbo].[Attachments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Attachment] varbinary(max) NOT NULL,
    Filler char(8000),
    Filler2 char(49)
);

ALTER TABLE  [dbo].[Attachments] DROP COLUMN Filler,Filler2

INSERT INTO [dbo].[Attachments]
([FileName],[Attachment])
VALUES
('Foo',0x010203)

Which Gives

Msg 511, Level 16, State 1, Line 12 Cannot create a row of size 8075 which is greater than the allowable maximum row size of 8060.

If this is the case then try rebuilding the table

ALTER TABLE [dbo].[Attachments] REBUILD
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Really well done, that man :) I'd had a similar thought but my trawls across Google led me to try DBCC CLEANTABLE. Your suggestion was spot on. Many thanks! – Richard Oct 03 '11 at 22:21
  • In-fact, its good practice to rebuild the table after structure is changed. It increases performance greatly! – Tejasvi Hegde Jul 05 '14 at 14:46
  • martin, i came across he same problem, sadly we are currently using sql 2005, is there a way to rebuilt the table without removing its contents? – Albert Laure Jun 03 '16 at 09:04
  • @user does alter table ... rebuild not exist in 2005? – Martin Smith Jun 03 '16 at 10:19
  • sadly no it does not exist. – Albert Laure Jun 06 '16 at 01:27
  • I'm running into the same issue. I've run CleanTable and rebuild on the table but still getting the error. I'm running on azure sql, anything that would cause azure sql to error out? – TWilly Oct 13 '17 at 14:25