Dealing with corruption occurred after you run “Alter Table” in MS SQL 2000

In SQL server when you need to modify any table, you use “Alter Table” Transact SQL command. This command modifies the table definition by altering, adding or dropping columns and constraints, or by disabling or enabling constraints and triggers. However, this is recommended method to make modifications in table definitions but sometimes, when you run “Alter Table” command you get incorrect results. You want columns that are not null but you get records that contain null columns.

Due to providing incorrect results and corruption and your database gets marked as suspect and becomes unavailable after that. When you try to run dbcc checkdb command, you find various errors appearing on your computer screen.

Usually, this problem occurs when there is a problem in synchronizing column status between syscolumns system table and the sysindexes system table. Also in cases where a column is a part of clustered index key that is null this problem may crop up.

How to deal with corruption occurred after running “Alter Table” command

To deal with this corruption issue, you need to use any of the below mentioned solution:

Solution 1:

Confirm database corruption

Before moving further, it is essential to check whether the database is corrupt or not. To check database corruption, run either the dbcc checkdb command or the dbcc checktable command.

• If command output recommends running the dbcc checkdb command that uses the repair_rebuild option to fix the problem, database is corrupt.

• When the command output recommends running the command that uses the repair_allow_data_loss option, you can be rest assured that the database is not corrupt.

Use repair_rebuild option

Once database corruption is confirmed, run the dbcc dbreindex command or the dbcc checktable command by using the repair_rebuild option.

Solution2:

Run the “Alter Table” Transact-SQL command to change a column that is a key of a clustered index from not null to null. After running “Alter Table” command, immediately run the dbcc dbreindex command or re-create the index on the table.

This way you can handle corruption issues occurred after running “Alter Table” command. But what if the problem still persists? In situations when SQL database is majorly corrupt and cannot be repaired using above mentioned procedure, you can take help of Kernel for SQL Recovery. This software efficiently repairs the corrupt SQL server database and recovers data from it.

Comments are closed.