Knowledge Base

Database corruption occurs after you run the "alter table" Transact-SQL command to change a column from not null to null in Microsoft SQL Server 2000

Article ID: 928810

Article Last Modified on 11/2/2007

APPLIES TO

Microsoft SQL Server 2000 Developer Edition
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 Personal Edition

Bug: #50000540 (SQL Hotfix)

SYMPTOMS

After you run the alter table Transact-SQL command to change a column that is not null to null, you receive incorrect results. This problem occurs if the column is a clustered index key in Microsoft SQL Server 2000. Additionally, you experience any of the following corruption symptoms:

You receive records that have null columns. However, you only want rows that have columns that are not null.
A database is marked as suspect. Additionally, the database becomes unavailable. In the SQL Server error log, you see runtime errors that resemble the following:

<Date> spid58 Error: 644, Severity: 21, State: 1
<Date> spid58 Could not find the index entry for RID '360000000002000101000f00383136' in index page (1:77), index ID 0, database 'testdb'.
<Date> spid58 Error: 3314, Severity: 21, State: 4
<Date> spid58 Error while undoing logged operation in database 'testdb'. Error at log record ID (5:141:239).
<Date> spid58 Error: 9001, Severity: 21, State: 1
<Date> spid58 The log for database 'testdb' is not available.
<Date> spid58 Error: 3314, Severity: 21, State: 5
<Date> spid58 Error while undoing logged operation in database 'testdb'. Error at log record ID (5:32:1).
<Date> spid17 Database 'testdb' (database ID 18) could not recover. Contact Technical Support.
If you run the dbcc checkdb command, you experience corruption errors that resemble the following:

Server: Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1977058079, index ID 1. The high key value on page (1:77) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:82).
Server: Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1977058079, index ID 1. The low key value on page (1:78) (level 0) is not >= the key value in the parent (1:75) slot 2.

Note You do not experience this problem in SQL Server 2005.

CAUSE

This problem occurs if the following conditions are true:

SQL Server does not synchronize column status between the syscolumns system table and the sysindexes system table.
A column is part of a clustered index key that may be null.

This problem may cause SQL Server to perform incorrect assumptions during data modification operations, such as the insert operation.

WORKAROUND

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

Make sure that the database is online. Then, run the dbcc dbreindex command or the dbcc checktable command by using the repair_rebuild option.
If the database is marked as suspect, telephone Microsoft Customer Support Services. For a complete list of Microsoft Customer Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support
Run the alter table Transact-SQL command to change a column that is a key of a clustered index from not null to null.

Note If you run the alter table Transact-SQL command, you must immediately run the dbcc dbreindex command. Or, you can re-create the index on the table.

STATUS

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

MORE INFORMATION

To determine whether database corruption has occurred, use one of the following methods:

Run either the dbcc checkdb command or the dbcc checktable command.

Notes
- The problem exists when the command output recommends running the dbcc checkdb command that uses the repair_rebuild option to fix the problem.
- The problem does not exist when the command output recommends running the command that uses the repair_allow_data_loss option.

Run the following query. The query determines whether some columns are part of an index key. The query also shows differences in the syscolumns table and in the sysindexes.status column:

print 'the following tables have indexes defined on a column that is nullable but sysindexes.status column says not nullable'
print 'this could lead to corruption or incorrect results'
select cast(object_name(i.id) as varchar(25)) 'object name',
cast ( i.name  as varchar(30)) 'index name' , * from sysindexkeys k inner join syscolumns c on (k.id = c.id and k.colid = c.colid)
inner join sysindexes i on (i.id=k.id and i.indid=k.indid)
where c.isnullable =1 and ((i.status & 0x200000) = 0)

Keywords: kbtshoot kbprb KB928810

Microsoft KB Archive/928810

Contents