January 3, 2011 at 3:56 pm
I ran an ALTER INDEX REBUILD statement on a SQL Server 2008 instance (10.0.2775) against a PK partition. up until last week, this worked without issue. now I receive the following error:
Msg 1944, Level 16, State 1, Line 1
Index '' was not created. This index has a key length of at least -1 bytes. The maximum permissible key length is 900 bytes.
The PK is clustered and the record is 1093 Bytes wide, however the indexed columns are only 38 Bytes ( composed of INT x4, VARCHAR(20), and BIT x2). Up until two weeks ago (Dec 22) this ran fine. I returned to work today (Jan 3) and the above error is thrown. Notice that the index name is listed as blank.
I ran a DBCC CHECKDB on the database and there were no issues found.
Has anyone run into this before and do you have any idea what is happening and/or how to fix it?
January 6, 2011 at 2:30 pm
This is strange as a clustered index can only have a max length of 900 bytes as in the message.
You can get higher than this with non-clustered indexes when using variable length columns and/or included columns.
I would suggest start investigating your current indexes and take it from there. The below may help you:
SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('table_name_with_issue')
SELECT * FROM sys.index_columns
WHERE object_id = OBJECT_ID('table_name_with_issue')
August 7, 2014 at 11:31 am
i've run into a similar problem, and after some experimentation have boiled it down to a simple example that reproduces the error, which seems to occur when you have a combination of (a) two or more bit columns in a clustered index and (b) a compressed nonclustered index that includes a varchar column.
this example reproduces it consistently (we're running 2008 R2 SP2):
create table test(b1 bit, b2 bit, c varchar(10))
create clustered index ci on test(b1, b2)
create nonclustered index nci on test(c) with (data_compression = page)
the table and clustered index creates work fine, but the nonclustered index create fails with the error
Msg 1944, Level 16, State 1, Line 1
Index 'nci' was not created. This index has a key length of at least -1 bytes. The maximum permissible key length is 900 bytes.
August 7, 2014 at 11:41 am
millerrich (8/7/2014)
i've run into a similar problem, and after some experimentation have boiled it down to a simple example that reproduces the error, which seems to occur when you have a combination of (a) two or more bit columns in a clustered index and (b) a compressed nonclustered index that includes a varchar column.this example reproduces it consistently (we're running 2008 R2 SP2):
create table test(b1 bit, b2 bit, c varchar(10))
create clustered index ci on test(b1, b2)
create nonclustered index nci on test(c) with (data_compression = page)
the table and clustered index creates work fine, but the nonclustered index create fails with the error
Msg 1944, Level 16, State 1, Line 1
Index 'nci' was not created. This index has a key length of at least -1 bytes. The maximum permissible key length is 900 bytes.
That is a very good little tidbit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2014 at 12:01 pm
Huh, I'm getting the same results when I run the query; I don't have Enterprise edition, so I can't compress data anyhow, but I get the -1 bytes message when I try the test query.
Is there an explanation for this behavior? I'd guess that it's because the clustered index is created first, and the table and clustered index both didn't have compression specified; since the nonclustered index is trying to use data compression, but must contain the clustered index, the mismatch in compression is causing the failure.
Or is it something else entirely? 😛
- 😀
August 7, 2014 at 12:06 pm
you get the same error if you create the clustered index compressed. also, if you create the nonclustered index first (before creating the clustered index) it works, but you then get the same error when you try to create the clustered index.
August 7, 2014 at 12:22 pm
Hrm. I was thinking it might have something to do with the way bit-type columns are stored, too; since they can collectively register as one or two bytes depending on the number of bit columns in a table, maybe that's playing foul with the compression algorithm?
I noticed that, if I try to add more bit columns to the table and clustered index, the -1 bytes keeps climbing, up until I hit -14 bytes for trying a 16-column clustered index on all bit columns and then attempting to create a nonclustered index on the varchar column.
This probably isn't too important, but now I'm kinda curious about what's going on internally to make this happen 😀
EDIT: Huh, now I think it definitely has something to do with the inherent bit-type compression. If I make one column in the clustered index a char column (so I have a fixed amount of data storage reserved), I can make the length 899, add 8 bit columns along with it to the clustered index, and fail the nonclustered creation with an invalid length of -7 bytes.
If I leave the length at 899 and add 9 bit columns instead, therefore going past the tipping point of 900 bytes on a clustered index's key length, I fail the nonclustered index creation due to a length overage, with SQL Server reporting that the max key length is 901. :crazy:
So, I guess the weird thing here is the bit-type compression; since the bits could theoretically compress to a lower value, the engine is stopping that from happening, even though it's not actually a problem? Although I can't figure out why it still thinks that's possible even with a fixed-length amount of data consumption :ermm:
- 😀
August 7, 2014 at 1:09 pm
i've opened a support case with ms on the issue. will be interesting to see what they come up with.
August 7, 2014 at 1:14 pm
Ah, gotcha. If you don't mind, post back here with what you find; I'm intrigued by the problem, trivial though it might be overall 😛
- 😀
August 7, 2014 at 1:21 pm
will definitely post back their response. it's actually not trivial for me; i hit the error trying to add a new bit column to an existing table that already contained another bit column. in my real-world case the table and indexes obviously contained a number of other more meaningful columns, but i came up with the simplified example in my first post by stripping away everything not directly related to the error to (hopefully) make it more clear.
i've worked around it for now by dropping the compression on the nc index, but that's not an ideal solution and i'd still like to know why it won't work under these conditions.
August 7, 2014 at 1:27 pm
Oh, I see! I thought the problem might only come up in relatively niche circumstances with bits, but it looks like it's a lot more widespread than that. That certainly does make it a good bit more important!
I'll tinker with it on my side a bit more, then; I don't foresee it being a problem in my current environment, but there could be a case when remembering this sort of thing will be handy.
- 😀
August 7, 2014 at 1:49 pm
Andrew Kernodle (8/7/2014)
(...) That certainly does make it a good bit more important!
i see what you did there 😉
August 7, 2014 at 1:57 pm
D'oh, that was totally unintentional :-P. I guess I've absorbed the ability to subconsciously fire off bad programming puns after trawling these forums enough 😀
- 😀
August 8, 2014 at 5:17 pm
microsoft reports that this bug was identified and fixed in the latest 2008 R2 SP2 cumulative update (CU13, build 10.50.4319.0, see http://support.microsoft.com/kb/2957174 for details).
we're currently on 2008 R2 SP2 CU7, so it looks like catching up on the CUs will resolve the problem for us.
August 9, 2014 at 5:23 am
Ah, I see. Good to know it wasn't intended functionality, and that there's a fix out there. My company is on 2008 R2 SP1, and we're probably never moving past that due to vendor restrictions, so it's something I'll have to keep in mind.
Thanks for posting back with your findings!
- 😀
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply