index entry exceeds the maximum length

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

index entry exceeds the maximum length

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-05-29 : 20:46:04

I'm seeing this error in my application log. Not quite sure how it started happening all of a sudden. I'm not quite sure where to start on this one.

Any suggestions greatly appreciated!

Thanks,
Mike123

Exception information:
Exception type: SqlException
Exception message: Operation failed. The index entry of length 1007 bytes for the index 'tblMessage25' exceeds the maximum length of 900 bytes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 02:14:41

Could you post the DDL for that table including all indexes? I suspect you've got an index over columns that could exceed 900 bytes if filled, for instance if you've got a composite index over 3 columns with each of them defined as varchar(500).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-30 : 12:48:41

If you are using SQL Server 2005, try to rewrite the index using the new INCLUDE keyword.

E 12°55'05.25"
N 56°04'39.16"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-05-30 : 17:13:43

If you have variable length columns (e.g., VARCHAR, et al) in your index and the maximum size exceeds the 900 byte limit, SQL Server will allow index and "hope" that your column data size does not cause the maximum index size to be exceeded. If it does, you get this error.

Example:

create table MyTable (
key1 char(890),
key2 varchar(20),
blah, blah, blah,

constraint PK_MyTable primary Key (key1, key2)
)

The potential exists for the key to exceed the 900 byte limit (890 + 20) but it won't necessareily exceed it. In the cases where the key2 column holds data with ten or less characters, you're fine. If key2 holds 'abcdefghijklm', however, you will exceed the limit and throw an error.

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 03:12:33

Hey Guys,

Bumping an old topic here as I got sidetracked on this one. Noticed this error popping up again so I have included my table DDL with the index.

How would I go about fixing this error ?

Thanks once again!
mike123

CREATE TABLE [dbo].[tblInstantMessage](
[InstantMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageToID] [int] NULL,
[MessageFromID] [int] NULL,
[Message] [varchar](1000) NULL,
[Date] [smalldatetime] NOT NULL,
[Checked] [tinyint] NULL,
[Mobile] [tinyint] NULL,
[deletedbySender] [tinyint] NULL,
[deletedbyRecipient] [tinyint] NULL,
[IP] [varchar](15) NULL,
CONSTRAINT [PK_tblInstantMessage] PRIMARY KEY CLUSTERED
(
[InstantMessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 05:48:01

Is this tblMessage25?

An index can only hold 900 bytes per record. It seems your column Message is part of some index, and it's ok until you acutally store more than 900 bytes in Message column.

Rewrite the indexes holding Message column and use the INCLUDE keyword.
Post the indexes using Message column here.

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 09:12:41

Hi Peso,

Good eye on the tables! Actually you made me realize that I am getting it on both tables. Since I have posted the structure for tblInstantMessage already I will continue on with that table. Then I can apply the same logic to the table "tblMessage"

The Error I am getting is this:

Exception information:
Exception type: SqlException
Exception message: Operation failed. The index entry of length 986 bytes for the index 'tblInstantMessage25' exceeds the maximum length of 900 bytes.

My Indexes on the table are as follows: (generated by index tuning wizard =\)

Thanks once again, your help is very much appreciated!
Mike123

/****** Object: Index [PK_tblInstantMessage] Script Date: 10/16/2008 05:54:02 ******/
ALTER TABLE [dbo].[tblInstantMessage] ADD CONSTRAINT [PK_tblInstantMessage] PRIMARY KEY CLUSTERED
(
[InstantMessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage24] Script Date: 10/16/2008 05:54:29 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage24] ON [dbo].[tblInstantMessage]
(
[MessageFromID] ASC,
[InstantMessageID] ASC,
[MessageToID] ASC,
[Message] ASC,
[Date] ASC,
[Mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage2424] Script Date: 10/16/2008 05:55:01 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage2424] ON [dbo].[tblInstantMessage]
(
[MessageFromID] ASC,
[InstantMessageID] ASC,
[MessageToID] ASC,
[Message] ASC,
[Date] ASC,
[Mobile] ASC,
[deletedbySender] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage25] Script Date: 10/16/2008 05:55:30 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage25] ON [dbo].[tblInstantMessage]
(
[MessageToID] ASC,
[MessageFromID] ASC,
[Date] DESC,
[InstantMessageID] ASC,
[Message] ASC,
[Mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage36] Script Date: 10/16/2008 05:55:53 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage36] ON [dbo].[tblInstantMessage]
(
[MessageToID] ASC,
[Checked] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage38] Script Date: 10/16/2008 05:57:23 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage38] ON [dbo].[tblInstantMessage]
(
[MessageFromID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [tblInstantMessage43] Script Date: 10/16/2008 05:58:11 ******/
CREATE NONCLUSTERED INDEX [tblInstantMessage43] ON [dbo].[tblInstantMessage]
(
[MessageToID] ASC,
[Checked] ASC,
[deletedbyRecipient] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 09:33:33

Rewrite all your NONCLUSTERED index which covers Message column, like this

CREATE NONCLUSTERED INDEX [tblInstantMessage25] ON [dbo].[tblInstantMessage] 
(
	[MessageToID],
	[MessageFromID],
	[Date] DESC,
	[InstantMessageID],
	[Mobile]
)
INCLUDE
(
	[Message]
) WITH (FILLFACTOR = 90) ON [PRIMARY]

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 09:37:05

Hi Peso,

Just wondering, does this mean I have to DROP and RECREATE ? (its a large table (10 million rows) and just want to make sure first)

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-16 : 09:41:41

Yes. You have to drop and recreate.

By the way... Do you really need Message column in the index?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 09:46:55

quote:
Originally posted by Peso

Yes. You have to drop and recreate.

By the way... Do you really need Message column in the index?

E 12°55'05.63"
N 56°04'39.26"

To be honest I am not really sure if I do. (just following what tuning wizard told me I needed =\)

I allow users to do searches based on the contents. I also run some "GROUP BY" queries to search for mass duplicate messages (spam)

Perhaps this is why I have it ?

Thanks again :)
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 11:15:35

also, just wondering, is there any performance hit or anything negative about using "INCLUDE" ?

thanks again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 01:46:15

What do Books Online say about the INCLUDE keyword?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources