Row size limit on large select statement

Question

Post reply

Row size limit on large select statement

todd.pagni

SSC Veteran

Points: 291
More actions
September 21, 2010 at 10:16 am

#238569

We are running very large select statements in SQL Server 2005/2008 and we are running into the following error: Cannot create a row of size 8170 which is greater than the allowable maximum of 8060
I have seen a lot of info on row size for insert/update, but this is happening on a large select (60k + in size, 70+ tables in the query). Has anyone else seen this issue? Any solution?
Thanks,
Todd

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Tim Parker SSCommitted Points: 1644 More actions · Answer 1

Todd,

Is it possible for you to post the estimated execution plan for this particular query?

😀

todd.pagni SSC Veteran Points: 291 More actions · Answer 2

I am unable to get an execution plan when the problem occurs

Tim Parker SSCommitted Points: 1644 More actions · Answer 3

The next question is has this query run for you in the past? Not going to make a real difference if it has, but will help you identify where to begin in your troubleshooting. Possibly an underlying table has had a data type modification that is now causing this issue.

I've never personally run into this in my work environment, but as the problem states, the data row exceeds the 8060 limit in SQL Server. This is the limit for row length on the data page, so there is no work around for it.

Here is a reference for you in SQL Server Books Online.

Estimating the Size of a Table: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/15c17c92-616f-402e-894b-907a296efe5f.htm

Hope this helps...

😀

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 4

if this just a select? maybe try finding the biggest col and doing a convert or cast on it so that it works

Does not make much since to me when it was inserted in the DB it fit into the page....seems like you should be able to get it out?

Hope someone else might be able to add something to help

todd.pagni SSC Veteran Points: 291 More actions · Answer 5

Thanks for having a look at this issue. The troubling part is that we do not have any rows in our database with over 8k. We are just trying to do a Select, so it must be some internal object or tempdb having the issue when running the select.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 6

456789psw

SSCarpal Tunnel

Points: 4728

September 22, 2010 at 10:06 am

#1225727

can you post the statement?

HowardW One Orange Chip Points: 29109 More actions · Answer 7

Seconded to post the statement and create table scripts - this is definitely SQL Server 2008?

rory.hunt 26140 Valued Member Points: 57 More actions · Answer 8

Hi Guys,

I am also having the same issue - no create or insert, but just a very large select. The select is auto generated from a .net app (nhibernate) and the query does seem to work OK if running against sparsely populated tables but bombs on a production like test.

To post the whole query would be huge - but I can zip it up and attach is this forum allows, however I have attached the trace - below. which may shed some light on the internals of SQL . . .

NHibernate: SELECT this_.FactAssetRegisterID as FactAsse1_176_84_, this_.COBDateID as COBDateID176_84_, this_.DirectionID as Directio3_176_84_, this_.AssetAvailableDateID as AssetAva4_176_84_, this_.AssetAmountUSD as AssetAmo5_176_84_,

--->>>>>>>> have truncated the rest of the fields - but it is all a select!

FROM dbo.ALO_FactAssetRegister this_ inner join dbo.ALO_DimSecurityInstrument alodimsecu2_ on this_.SecurityInstrumentID=alodimsecu2_.SecurityInstrumentID inner join dbo.ALO_DimEquityInstrument alodimequi3_ on this_.EquityInstrumentID=alodimequi3_.EquityInstrumentID

--->>>>>>>> have truncated the rest of the tables- but it is all a select!

NHibernate.Util.ADOExceptionReporter: 2010-10-01 08:54:47,843 [(null)] [] [7]

WARN System.Data.SqlClient.SqlException: Cannot create a row of size 8321 which is greater than the allowable maximum of 8060.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.HasMoreRows()

at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

at System.Data.SqlClient.SqlDataReader.Read()

at NHibernate.Driver.NHybridDataReader.Read()

at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)

at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)

at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)

NHibernate.Util.ADOExceptionReporter: 2010-10-01 08:54:47,890 [(null)] [] [7]

ERROR Cannot create a row of size 8321 which is greater than the allowable maximum of 8060.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 9

someone found this answer on another forum....but it does make since...hope it helps..let us know

"problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length."