Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server XML

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.

Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...

CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)

The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.

Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO

and then...

Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.

Database restored from scratch and schema collection created, operations=

Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.

Restore Database as before and create schema collection as before

Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.

If you delete all the rows then the ALTER COLUMN completes without error.

A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.

All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.

[10949 byte] By [MarkDooley] at [2007-12-24]
«« Can not deploy calculation...
»» Update Excel Data
# 1

Hi Mark,

It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@microsoft.com.

Thanks

Jinghao Liu

SQL Server Relational Engine XML team

JinghaoLiu at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 2

Hi

FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro

Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection

I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup

I have not had a problem with update since I used the new filegroup

aero1 at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 3

Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.

Thanks

Jinghao Liu - SQL Server Engine

JinghaoLiu at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 4

Database information was sent to Jianghao Liu directly as requested. The following email was then received...

A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!

Have a nice weekend!

Jinghao

MarkDooley at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 5

This reply has just been received from MSFT:

This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.

If you are developing application base on continually ALTER COLUMN, then you need to redesign it.

Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?

So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?

And we wonder why people go anti-MSFT and turn to other platforms.

Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!

~swg

swg at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 6

I agree with swg that this response is unacceptable.

I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed

It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.

It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:

  • The minimum number of schemas needed are put into each collection - giving smaller collections
  • Separate the data into different tables - typed by different collections
  • Plan on exporting/reimporting the data if you need to do an alter - the previous 2 bullets will keep the data involved to the minimum possible

I am sure that Microsoft ought to be able to provide something better in this area - can't the export/import type operation be performed under the covers by the alter?

aero1

aero1 at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 7

Hi!

Found a workaround. Good for solving other problems, too.

The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.

Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).

The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.

The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.

When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.

Hope, I could help.

Bye:

Barrez

Barrez at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 8

Thank you for your help

Regards

Mark Dooley

MarkDooley at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
# 9

Hey thanks for this,

Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?

I'll get Mark to mark your post as the Answer!

Cheers,

~swg

swg at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
SQL Server
SQL Server XML

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified