2

I am stuck and I can not figure out. Does anyone have any ideas? I would greatly appreciate any help.

I have a table in SQL Server 2008 with the following defintion:

I can insert/delete/update without any error. However, when i run a select statement that filter out ONE single item, i got the following error:

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

If I remove the where condition a15.MEMBER_PARENT_LEVEL_ID4 in (91329) it ran fine. If I add 5 or 10 criterias it ran fine, including the one that is throwing the error if you add it by itself.

[MSTR_PROD_HIER_ID] [int] NOT NULL,
[MSTR_PROD_HIER_DESC] [varchar](50) NULL,
[MEMBER_GROUP_LEVEL_ID1] [int] NULL,
[MEMBER_GROUP_LEVEL_DESC1] [varchar](60) NULL,
[MEMBERKEY_SORT1] [int] NULL,
[MEMBER_GROUP_LEVEL_ID2] [int] NULL,
[MEMBER_GROUP_LEVEL_DESC2] [varchar](60) NULL,
[MEMBERKEY_SORT2] [int] NULL,
[MEMBER_GROUP_LEVEL_ID3] [int] NULL,
[MEMBER_GROUP_LEVEL_DESC3] [varchar](60) NULL,
[MEMBERKEY_SORT3] [int] NULL,
[MEMBER_LEVEL_ID4] [int] NULL,
[MEMBER_LEVEL_DESC4] [varchar](60) NULL,
[MEMBER_LEVEL_DESC4_1] [varchar](60) NULL,
[MEMBER_PARENT_LEVEL_ID1] [int] NOT NULL,
[MEMBER_PARENT_LEVEL_DESC1] [varchar](60) NULL,
[SORT1] [int] NULL,
[MEMBER_PARENT_LEVEL_ID2] [int] NULL,
[MEMBER_PARENT_LEVEL_DESC2] [varchar](60) NULL,
[SORT2] [int] NULL,
[MEMBER_PARENT_LEVEL_ID3] [int] NULL,
[MEMBER_PARENT_LEVEL_DESC3] [varchar](60) NULL,
[SORT3] [int] NULL,
[MEMBER_PARENT_LEVEL_ID4] [int] NULL,
[MEMBER_PARENT_LEVEL_DESC4] [varchar](60) NULL,
[SORT4] [int] NULL,
[CLASS_DESC] [varchar](60) NULL,
[CLASS_DESC1] [varchar](50) NULL,
[TOTAL_BRAND_IND] [int] NOT NULL
}


{
select    a11.PlanFormat  PlanFormat,
max(a18.FormatName)  FormatName,
a15.MEMBER_PARENT_LEVEL_ID2  MEMBER_PARENT_LEVEL_ID2,
max(a15.MEMBER_PARENT_LEVEL_DESC2)  MEMBER_PARENT_LEVEL_DESC2,
a15.MEMBER_PARENT_LEVEL_ID3  MEMBER_PARENT_LEVEL_ID3,
max(a15.MEMBER_PARENT_LEVEL_DESC3)  MEMBER_PARENT_LEVEL_DESC3,
a15.MEMBER_PARENT_LEVEL_ID4  MEMBER_PARENT_LEVEL_ID4,
max(a15.MEMBER_PARENT_LEVEL_DESC4)  MEMBER_PARENT_LEVEL_DESC4,
max(a15.CLASS_DESC)  CLASS_DESC,
max(a15.CLASS_DESC1)  CLASS_DESC1,
a16.MSTR_SEASON_ID  MSTR_SEASON_ID,
max(a16.MSTR_SEASON_DESC)  MSTR_SEASON_DESC,
max(a16.MSTR_SEASON_DESC1)  MSTR_SEASON_DESC1,
a17.MSTR_STORE_ID  MSTR_STORE_ID,
max(a17.MSTR_STORE_DESC1)  MSTR_STORE_DESC1,
a13.MSTR_C_MNTH_ID  MSTR_C_MNTH_ID,
max(a13.MSTR_C_MNTH_DESC)  MSTR_C_MNTH_DESC,
a13.MSTR_CALENDAR_GRP_ID  MSTR_CALENDAR_GRP_ID,
max(a13.MSTR_CALENDAR_GRP_DESC)  MSTR_CALENDAR_GRP_DESC,
a12.YEAR_NBR  YEAR_NBR,
a11.PlanNo  PlanNo,
max(a18.PlanName)  PlanName,
sum((a11.Value1821 / 1000.0))  ACT_PROJ_SLS_DOL
from    A_MSTR_PLAN_FCT    a11
join    A_MSTR_CALENDAR_MONTH_XREF    a12
on     (a11.Member2 = a12.Member2)
join    A_MSTR_CALENDAR_MONTH    a13
on     (a12.DIM_TIME_ID = a13.DIM_TIME_ID and 
a12.YEAR_NBR = a13.YEAR_NBR)
join    A_MSTR_PRODUCT_LEVEL_3    a14
on     (a11.MEMBER0 = a14.P_MEMBER3)
join    A_MSTR_PRODUCT_LEVEL_3_PARENT    a15
on     (a14.MEMBER_LEVEL_ID4 = a15.MEMBER_LEVEL_ID4 and 
a14.MSTR_PROD_HIER_ID = a15.MSTR_PROD_HIER_ID)
join    A_MSTR_SEASONALITY    a16
on     (a11.MEMBER3 = a16.MEMBER3)
join    A_MSTR_STORE    a17
on     (a11.MEMBER1 = a17.MEMBER1)
join    A_MSTR_PLAN    a18
on     (a11.PlanNo = a18.PlanNo and 
a12.YEAR_NBR = a18.YEAR_NBR and 
a13.YEAR_ID = a18.YEAR_ID)
where    (a11.PlanNo in (1547)
and a15.MEMBER_PARENT_LEVEL_ID4 in (91329))
group by    a11.PlanFormat,
a15.MEMBER_PARENT_LEVEL_ID2,
a15.MEMBER_PARENT_LEVEL_ID3,
a15.MEMBER_PARENT_LEVEL_ID4,
a16.MSTR_SEASON_ID,
a17.MSTR_STORE_ID,
a13.MSTR_C_MNTH_ID,
a13.MSTR_CALENDAR_GRP_ID,
a12.YEAR_NBR,
a11.PlanNo
}

This is a follow up. to my previous post. As i mentioned below, this error goes away after i dropped the table, perform DBCC clean up and recreate the table. Now, it is coming back but this time, only when you select two or more criterias. The problem seems to be with the where condition where it filter out my product. My product table does not have any nvarchar or varbinary. I am so frustrated... :-(

user2642833
  • 31
  • 1
  • 5
  • this might give you an insight: http://stackoverflow.com/questions/7631546/entity-framework-row-size-greater-than-allowable-maximum-row-size-of-8060 – Giannis Paraskevopoulos Aug 12 '13 at 17:39
  • thank jyparask... this was helpful. Although my problem is slightly different, it gave me an idea. I am able to insert data into my tables. It doesn't through any error. I dropped 10 columns, 7 INT and 3 VARCHAR. Then, i ran the DBCC CLEANTABLE and verifying results. What's weird is that my avg space used in % increased by ~.5 % from 95.3 to 96.4521868050408 and the query ran without this error. This only happended when you select certain values. The actual data is shorter than the record with the max length. Once again, thank you very much. – user2642833 Aug 12 '13 at 19:37
  • Why would a **select** fail with that error?! This is a DML error. Are you sure this exact statement is the cause? Do you understand why it can't be the cause? – usr Aug 12 '13 at 20:31
  • I took the SQL statement and ran it with the where clause. It throws this error. I remove the where clause and it doesn't throw the error anymore. The SQL ran fine. I understand the error.. However, i do not understand why I am getting this error. Furthermore, I checked the record length for each selection and it is no where near the max. This error is really frustrating. My users are complaining ever second... I have never experience this with other db. – user2642833 Aug 23 '13 at 14:32
  • If you could create a SQLFiddle at http://sqlfiddle.com/ with the tables, some data and the query that produces the problem, it would be easier to help you. – MicSim Aug 23 '13 at 15:08
  • 2
    Please post the execution plan as well. [You may well be hitting the issue here](http://blogs.msdn.com/b/craigfr/archive/2009/06/24/maximum-row-size-and-query-hints.aspx) – Martin Smith Aug 23 '13 at 15:44
  • 1
    @usr - It might be able to happen with a `SELECT` if the plan uses a work table and the row would be too wide. Not sure the link above gives a different error message in that case though. – Martin Smith Aug 23 '13 at 15:51
  • @MartinSmith I have seen that and there is a reference to sorting in the message. – usr Aug 23 '13 at 18:42
  • Is the query using a different queryplan depending upon which values are specified. e.g. is one query plan causing the problem when another doesn't. The queryplan could be influenced by the table statistics causing a different type of plan. – Steve Ford Aug 29 '13 at 13:30
  • Uck... that is **not** how you do hierarchy in SQL Server. Look up [hierarchyid](http://technet.microsoft.com/en-us/library/bb677290.aspx). – Aaronaught Sep 24 '13 at 00:03

2 Answers2

0

I would suggest modifying the query to remove the IN clause(s) and replace it with additional joins or a WHERE EXISTS. This may generate a more efficient query plan which avoids the problem you're seeing.

John Wu
  • 50,556
  • 8
  • 44
  • 80
0

If your physical data size is less than 8060 then too you are getting this error, try following command.

DBCC CLEANTABLE

For example:

DBCC CLEANTABLE (Databasename,'tablename', 0)
WITH NO_INFOMSGS;
Bista
  • 7,869
  • 3
  • 27
  • 55
vijay
  • 11
  • 3