SQL Database corruption caused after running ALTER TABLE

Thursday Jul 29, 2010

SQL Server is a relational model database and as we know administrators use T-SQL and ANSI SQL as their primary query languages. Talking of ALTER TABLE, this statement enables a user to rename an existing table. Moreover, this statement can be successfully used to add, modify or drop a column from an existing table. Using the ALTER command for changing a table is quite different from altering other kinds of objects. Moreover, you can use ALTER TABLE to alter just one table property without actually changing every other property about the table.

Whenever you need to change the value of column in SQL Server database to NULL value, you run ALTER TABLE command line. However, it’s not necessary that you would get the desired result. Usually, after you run the ALTER TABLE Transact-SQL command to change a column value from not null to NULL, you might receive incorrect results ending in SQL database corruption. Following are some of the symptoms that arise after the command line ALTER TABLE is run:

  • You receive records that have null columns. However, you only want rows that have columns that are not null.
  • Unavailable or suspected SQL database
  • Numerous SQL Server error log and runtime errors like,
    1. Error: 644, Severity: 21, State: 1
      Could not find the index entry for RID…
    2. Error: 3314, Severity: 21, State: 4
      Error while undoing logged operation in database ‘testdb’.
    3. Error: 9001, Severity: 21, State: 1
      The log for database ‘testdb’ is not available.
    4. Error: 3314, Severity: 21, State: 5
      Error while undoing logged operation in database ‘testdb’.

Moreover, running dbcc checkdb command, you experience more corruption errors. This problem occurs if the column is a clustered index key in MS SQL Server 2000. Or, SQL Server does not synchronize column status between the syscolumns system table and the sysindexes system table. All this results in SQL database corruption and poses great problems for the users.

To resolve this SQL database corruption, use any of the following methods:

  • Run the dbcc dbreindex command or the dbcc checktable command by using the repair_rebuild option.
  • Run the ALTER TABLE Transact-SQL command to change a column that is a key of a clustered index from not null to null.

If still you are facing SQL database corruption then use SQL recovery tool to repair SQL database .MDF file. Using these solutions might prove to be helpful for some, but those having little knowledge about SQL are suggested not to take any step without any professional advice. Even the slightest of mistake can make your data lost forever.

Rely on some professional tool to Repair SQL. These tools are devised to ensure that novice users can repair MDF file too and access their important data within minimal amount of time. Repair SQL tool resolves SQL database corruption, repairs damaged primary, foreign and unique keys and recovers all objects of SQL database such as triggers, stored procedures, views, rules, defaults, tables and records etc.

Leave a Reply