Wednesday, July 22, 2009

Resolving 8905 Error Message in SQL

DBCC (Database Console Commands) commands are vital for T-SQL (Transact-SQL) as they check logical and physical integrity of all objects under a selected database. The DBCC CHECKDB statement enables the user to catch and repair all the possible errors that can occur while accessing a SQL database. A DBCC CHECKDB command uses three parameters (repair_allow_data_loss, repair fast and repair rebuild) to perform absolute repair of the SQL database. However, there are certain issues that the DBCC CHECKDB command can not resolve. These issues make the data saved in the SQL database inaccessible. To access the SQL database and perform complete repair and restoration, if the database user has not created any back up, an efficient SQL Repair tool is required.

While shrinking the SQL database using DBCC SHRINKDATABASE command, the database user may encounter the following error message:

“Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.”

After the above error message appears, the extent in the SQL database becomes inaccessible. An extent in SQL database is the basic unit in which space is allocated to tables and indexes. These types of error messages are normally accompanied with one or more occurrences of error 8909. Furthermore, these errors are mainly caused in multi processor environments. The main cause of the above error message is that database user is using the shrink database and reindexing operation simultaneously. To prevent the SQL database from the above issue, the database user needs to reschedule the operations to execute at different times and turn off the Auto shrink option for that particular database.

To resolve the above issue and access the SQL database, the user needs to execute DBCC CHECKDB command with repair_allow_data_loss option. This command performs complete repair and also includes allocation and de-allocation of pages for rectifying allocation errors and deletion of corrupted text. However, this repair utility can also result in loss of data. In such scenarios, the user needs to recover the lost data by using effective SQL database recovery application. Such SQL Recovery software ensure complete repair and recovery of lost SQL database components by employing advanced and powerful scanning algorithms.

Stellar Phoenix SQL recovery is an excellent SQL Recovery application that performs complete and orderly recovery of SQL objects. It provides interactive and intuitive graphical user interface. It is compatible with MS SQL Server 2005 and MS SQL Server 2000. It also recovers and restores the back up files of MS SQL Server.

No comments:

Post a Comment