Tuesday, March 9, 2010

Database Corruption After Running Alter Table Command on SQL Server Table


Are you getting incorrect results when you run “alter table” T-SQL (Transact-SQL) command for changing a column to null that is not null? The problem is caused if table column is the clustered index is in MS SQL Server 2000. This behavior may corrupt the MS SQL Server database (MDF) and make all your valuable data inaccessible. In such circumstances, you need to opt for SQL Database Recovery solutions, if an updated backup is not in place.

Along with the incorrect results of alter table command, you may also come across the below database corruption symptoms:

You get the records, which have null columns. But, you want only the rows, which have columns that aren't null.
The MDF file is marked as suspect. Furthermore, the SQL Server database becomes unavailable. You see some runtime errors in the error log of SQL Server.
If you check the logical and physical integrity of the database through DBCC CHECKDB command, it gives you the below corruption errors:

“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.”

This behavior renders your database inaccessible. In order to gain access of your valuable data, you need to find out the cause of this problem and perform SQL Recovery by sorting it out.

Cause

This issue occurs if underwritten conditions are true:

MS SQL Server can't synchronize column status between sysindexes system table and syscolumns system tables.
Column is part of clustered index key, which may be null.

Solution

You can fix it by running dbcc dbreindex command by using repair_rebuild option. However, this method can not Recover SQL database.

In such situations, MS SQL Recovery software come for your help. These are powerful third-party applications, which methodically scan entire database to retrieve inaccessible data from it. They are totally safe and easy to use.

SQL Recovery is the most advanced solution to handle all types of SQL Server database corruption issues. It recovers all database objects such as tables, reports, formats, stored procedures, constraints, triggers, and more. The software is designed for Microsoft SQL Server 2008, 2005, and 2000.

No comments:

Post a Comment