DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 974985 - Last Review: April 26, 2010 - Revision: 2.0

Microsoft distributes Microsoft SQL Server 2005 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release.

SYMPTOMS

Consider the following scenario:
  • In Microsoft SQL Server 2005, you have a table that contains a column with the XML data type.
  • You create an XML index on the column.
  • You execute an ALTER INDEX statement with the REBUILD clause on the XML index.
  • You run a DBCC CHECKDB statement or a DBCC CHECKTABLE statement on the table.
In this scenario, orphan data corruptions are reported, and you receive the following error message:
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 965578478, index ID 1, partition ID 72057594042515456, alloc unit ID 72057594052083712 (type LOB data). The off-row data node at page (1:569), slot 0, text ID 1881079808 is not referenced.

There are 3 rows in 1 pages for object "sys.xml_index_nodes_<xxxxxx>".

RESOLUTION

The fix for this issue was first released in Cumulative Update 6 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
974648  (http://support.microsoft.com/kb/974648/LN/ ) Cumulative update package 6 for SQL Server 2005 Service Pack 3
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
960598  (http://support.microsoft.com/kb/960598/LN/ ) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

WORKAROUND

To work around this problem, use one of the following methods:

Method 1

Drop and then re-create the XML index instead of running the ALTER INDEX statement:

--------START OF THE SCRIPT--------------

----------Drop table named temp if it exists----------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp]') AND type in (N'U'))
DROP TABLE [dbo].[temp]


------------Create a tenp table to keep the information about existing XML indexes------------
Create table Temp (object_id int, index_id int, object_name varchar(256), IndexName nvarchar(256),Name nvarchar(256),
Secondary_type_desc nvarchar (240),Secondary_type char(1),Using_Xml_index_id int)


------------Insert XML index information into temp table----------------
Insert Into Temp
SELECT i.object_id,i.index_id,object_name(i.object_id)as object_name, i.name as IndexName
,c.name,i.Secondary_type_desc,i.Secondary_type,i.using_xml_index_id 
FROM sys.xml_indexes AS i
INNER JOIN sys.index_columns AS ic 
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
Order by i.index_id



-------------Adding a new column for Manipulation------------
Alter table temp add IndName varchar(256) NULL;


-------------Updating value in column for Manipulation------------

Update Temp Set Secondary_type_desc = 'APRIMARY' Where Secondary_type_desc IS NULL



-------------Updating value in column for Manupilation------------

Update a set a.IndName = b.IndexName from temp a inner join temp b on a.using_xml_index_id = b.index_id and a.object_id = b.object_id



-------------Set Ansi_padding ON to create XML indexes------------

Set Ansi_Padding ON



--------Cursor to Drop existing XML Indexes------------

DECLARE XMLIndexDrop CURSOR 
READ_ONLY
FOR select 'Drop Index '+ IndexName +' on '+Object_name from temp where Secondary_type is null
DECLARE @DropString varchar(400)

OPEN XMLIndexDrop
FETCH NEXT FROM XMLIndexDrop INTO @DropString
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
exec(@DropString)
PRINT @DropString
END
FETCH NEXT FROM XMLIndexDrop INTO @DropString
END

CLOSE XMLIndexDrop

DEALLOCATE XMLIndexDrop
GO



--------Cursor to Create XML Indexes-------------

DECLARE XMLIndexCreate CURSOR 
READ_ONLY 
FOR Select 'CREATE ' + Case when Secondary_type_desc = 'APRIMARY' then 'PRIMARY XML'
Else 'XML' END + ' INDEX ' + IndexName +' on ' + object_name + ' ('+name+')'
+ Case when Secondary_type_desc = 'APRIMARY' then ''
Else ' USING XML INDEX ' + IndName + ' FOR ' + Secondary_type_desc END 
From Temp Order By Secondary_type_desc Asc

DECLARE @CreateString varchar(400)
OPEN XMLIndexCreate

FETCH NEXT FROM XMLIndexCreate INTO @CreateString 
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
exec(@CreateString )
PRINT @CreateString 
END
FETCH NEXT FROM XMLIndexCreate INTO @CreateString 
END


CLOSE XMLIndexCreate
DEALLOCATE XMLIndexCreate
GO


--------Closing the cursor--------------


--------END OF THE SCRIPT-------------- 



Method 2

Drop all XML indexes, and then run the ALTER INDEX statement on the indexes in the table. Then, re-create the XML indexes.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

REFERENCES

For more informationabout the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
935897  (http://support.microsoft.com/kb/935897/ ) An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more informationabout how to obtain SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:
913089  (http://support.microsoft.com/kb/913089/ ) How to obtain the latest service pack for SQL Server 2005
For more information about the new features in SQL Server 2005 Service Pack 3 (SP3) and about the improvements in SQL Server 2005 SP3, visit the following Microsoft Web site:
http://go.microsoft.com/fwlink/?LinkId=131442 (http://go.microsoft.com/fwlink/?LinkId=131442)
For more informationabout the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499  (http://support.microsoft.com/kb/822499/ ) New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684  (http://support.microsoft.com/kb/824684/ ) Description of the standard terminology that is used to describe Microsoft software updates

APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Keywords: 
kbsurveynew kbexpertiseadvanced kbqfe kbfix KB974985
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support