When you update a value in a variable-length column in a table of a database that was upgraded from Microsoft SQL Server 2005, data in another variable-length column in the same row is deleted.
This issue occurs in Microsoft SQL Server 2014 and Microsoft SQL Server 2012. The following scenario may expose a database to this issue. This list represents a sequence of events:
-> A database is created in SQL Server 2005.
-> In SQL Server 2005, you alter a table, and then you drop one or more variable-length columns at the end of the table definition.
-> You add new variable-length columns that have a similar data type and length of the dropped columns.
-> The database is upgraded from SQL Server 2005 to SQL Server 2014 or SQL Server 2012.
-> You later update the value of another variable-length column, and the updated data has to overflow to a new page.
-> Lets follow the below demo to reproduce the issue. Create the below database and objects in SQL server 2005.
use master
go
create database test_dropCol
go
use test_dropCol
go
CREATE TABLE [dbo].test(
[Id] [int] IDENTITY(1,1) NOT NULL,
col1 [nvarchar](18) NOT NULL,
col2 [datetime] NOT NULL,
col3 [varchar](max) NULL,
col4 [varchar](max) NULL,
col5 [varchar](max) NULL,
CONSTRAINT[PK_ID]PRIMARY KEYCLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
— Drop the column
BEGIN TRANSACTION
GO
ALTER TABLE dbo.test
DROP COLUMN col5
GO
COMMIT
— Looking at the leaf_offset
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedCols
FROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)
INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)
ON sipc1.[partition_id] = sipc2.[partition_id] AND
sipc1.leaf_offset = sipc2.leaf_offset AND
sipc1.leaf_offset< 0 AND
sipc1.is_dropped < sipc2.is_dropped
INNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];
go
select sipc.partition_column_id, c.name,c.max_length, c.system_type_id,sipc.is_dropped, sipc.leaf_offset
from sys.system_internals_partition_columns sipc left outer join sys.partitions p on sipc.partition_id=p.partition_id
left outer join sys.columns c on c.object_id = p.object_id and c.column_id = sipc.partition_column_id
where p.object_id=object_id(‘test’)
go
— Creating another column
BEGIN TRANSACTION
GO
ALTER TABLE dbo.test ADD
col6 varchar(MAX) NULL
GO
COMMIT
— Looking at the leaf_offset again
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedCols
FROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)
INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)
ON sipc1.[partition_id] = sipc2.[partition_id] AND
sipc1.leaf_offset = sipc2.leaf_offset AND
sipc1.leaf_offset< 0 AND
sipc1.is_dropped < sipc2.is_dropped
INNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];
go
select sipc.partition_column_id, c.name,c.max_length, c.system_type_id,sipc.is_dropped, sipc.leaf_offset
from sys.system_internals_partition_columns sipc left outer join sys.partitions p on sipc.partition_id=p.partition_id
left outer join sys.columns c on c.object_id = p.object_id and c.column_id = sipc.partition_column_id
where p.object_id=object_id(‘test’)
go
— I could see -4 leaf offset for the newly created column and also the deleted column. The select query listed in “More Information” in KB3120595 returns that table test now.
-> Execute the below insert (we are also populating the last column which is having same offset as dropped column)
insert into test values(replicate (‘a’, 18),getdate(),replicate (‘a’, 8000),replicate (‘a’, 8000), replicate (‘a’, 8000))
go
select * from test
go
2) Take backup of this database and restore in SQL server 2012 or SQL server 2014 (on the build that doesn’t have the fix mentioned in 3120595)
3) Now execute the below query to reproduce data loss issue in SQL server 2012 or SQL server 2014– (Note – we are updating column before the new column that has same offset as dropped column)
begin tran
select * from test
go
update test
set col4 = replicate(‘a’, 8000)
go
select * from test
go
Workaround
Important Apply this workaround when you upgrade the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012 and before you let UPDATE statements be run in the upgraded database.
To work around this issue if you drop columns in a table, make sure that you do either of the following before you update or delete rows:
-> Rebuild the clustered index by using ALTER INDEX (…) REBUILD
-> Rebuild the heap by using ALTER TABLE (…) REBUILD
Resolution
Note This fix only prevents future occurrences of the issue.
Important If you are upgrading the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012, make sure that you refer to the “Workaround” section.
This issue was first fixed in the following cumulative update for SQL Server:
-> Cumulative Update 13 for SQL Server 2014
-> Cumulative Update 6 for SQL Server 2014 Service Pack 1
-> Cumulative Update 10 for SQL Server 2012 Service Pack 2
-> Cumulative Update 2 for SQL Server 2012 Service Pack 3
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.