Causes of Error 823 in SQL Server and How to Fix it

Do you encounter Input/Output (I/O) errors while accessing a MS SQL Server database? Are you unable to run queries on or access data from MDF (Master Database File)? This behavior indicates system or hardware problems with SQL Server that corrupt the database and renders it inaccessible. It leads to critical data loss situations. In order to extract data from Microsoft SQL Server in such situations, you need to opt for SQL Recovery solutions.

Hardware and system problems in SQL Server are generally indicated by an error message, similar to the following one:

“Error 823
I/O error detected during at offset in file ''”

You come across this error message when SQL Server attempts to read data from and write data to the database files. Format of this error message can be different, depending upon the version of SQL Server.

Cause

You come across this error message, if any of the below conditions are true:
Operating system error.
I/O logical check failure.
MS SQL Server database corruption.

If any of the above situations occur, it becomes essential to perform SQL Server Recovery by resolving it, to retrieve inaccessible data.

Solution

Try out the below things to fix up this issue, as per the cause of the the problem:

Operating system error: The read Windows API (Application Programing Interface) call or the write API call is unsuccessful and MS SQL Server experiences operating system errors, which is related to Windows API call. Run operating system integrity checking tools. Following is the example of operating system error:

“2003-07-28 09:01:27.38 spid75 Error: 823, Severity: 24,
State: 2
2003-07-28 09:01:27.38 spid75 I/O error 1117 (The request could not be performed because of an I/O device error.) detected during read at offset 0x0000002d460000 in file 'e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF'”
I/O check failure: If specified logical data checks are unsuccessful, you may encounter this problem. Run DBCC CHECKDB to solve this issue.
Database corruption: Restore the database from recent backup. If the backed is unusable, Recover SQL database through third-party applications.

The SQL Database Recovery software are powerful enough to handle most of the database corruption issues. The applications use high-end scanning methods to ensure absolute recovery. They are incorporated with rich graphical user interface to provide quick and easy recovery.

SQL Recovery is the most advanced and powerful utility that is able to effectively handle all database corruption scenarios. The software works well with Microsoft SQL Server 2008, 2005, and 2000. It restores all MDF file objects such as tables, reports, forms, constraints, stored procedures, triggers, data types, and more.

Article Source :- http://hubpages.com/hub/Causes-of-Error-823-in-SQL-Server-and-How-to-Fix-it

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.

MS SQL Server Database Corruption May Cause Access Violation

Are you facing Access Violation error in MS SQL Server? Are you unable to access the database and its objects? Access Violation is a situation where application can not access the data because the data is either damaged or the application does not own the data. In both the cases, your database remains inaccessible and unusable. This behavior may lead to significant data loss and needs MS SQL Recovery to be fixed.

In a practical scenario, consider:
In MS SQL Server 2008, a query contains the following clauses:
Union all
Group by
Inner join

The database query uses parallel execution plan.
You execute the query.

In this case, you may come across an Access Violation. Additionally, you may encounter the below error message:

“Location: xbmp.cpp:2038
Expression: 0 != pxteBitmap
Msg 3624, Level 20, State 1, Line 2

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.”

After the above error, database can not be accessed. In order to gain access of your SQL Server database, you need to carry out SQL Server Recovery by fixing this behavior.

Cause

As stated in the error message, you may have encountered such situations due to application malfunction or database corruption. Database corruption may occur after virus infection, unexpected system shutdown, operating system malfunction, and other similar situations.

Resolution

Consider running DBCC CHECKDB command to get your database repaired. It is an inbuilt utility in SQL Server that checks integrity and consistency issues of MDF (Master Database File).

However, if this tool can not fix the problem, third-party SQL Database Recovery software are always there for your help. These are advanced tools, which methodically scan entire database and ensure absolute SQL Recovery in most of the database corruption situations.

SQL Recovery is the most efficient tool to ensure absolute recovery of your damaged database. The software works well with Microsoft SQL Server 2008, 2005, and 2000. It restores all database objects such as tables, reports, forms, queries, stored procedures, triggers, and constraints.

Article Source :- http://hubpages.com/hub/MS-SQL-Server-Database-Corruption-May-Cause-Access-Violation

Tips to resolve “Extent E_ID in database ID DB_ID...”

GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and IAM (Index Allocation Map) pages are three very important building blocks of SQL Server metadata structure. A GAM page contains one bit for each extent, where each bit states if the extent is already allocated or is available for allocation. Similarly, SGAM page too consists of one bit for every extent, which states whether the extent is mixed or dedicated, and contains one or more allocated pages. An IAM page again has one bit per dedicated extent, which is assigned to the index for which the IAM is a part. A systematic combination of these bits assures that the database is perfectly fine and is in usable state. However, the combination becomes damaged in case the database gets logically or physically corrupted. To overcome such situations, the user needs to opt for SQL Database Recovery solutions.

Below is the error message explaining the above scenario:

“Extent E_ID in database ID DB_ID is marked allocated in the GAM, but no SGAM or IAM has allocated it.”

After the above error message pops up, the database records become inaccessible. The above error message occurs due to following reasons:

When the extent is dedicated and also mixed.
When the extent is dedicated and also unallocated.
When the extent is mixed and also unallocated.
When the extent is dedicated, mixed, and also unallocated.

Resolution:

To resolve the above error message and to access database records, user will need to perform below actions:

Check for physical corruption of database by viewing application logs, SQL Server log, and system logs. To fix any hardware-related issue, the user will need to change the damaged system component.
Restore the data from an updated backup.

Check for logical corruption in database by executing DBCC CHECKDB command. This will enable the user to determine the exact extent of corruption. To fix the logical corruption, the user will need to run the same command with a repair clause. If the logical corruption persists, then s/he needs to take help of advanced SQL Database Repair application. A SQL Recovery tool employs effective repairing techniques to resolve all logical corrupted databases.

SQL Recovery repairs and restores MS SQL Server 2008, 20005, and 2000 databases, post any logical corruption scenario. The read only SQL Repair utility is safe-to-use as it does not modify the original database while repairing. It supports Windows 7, Vista, 2008, XP, 2003, and 2000 operating systems.

Article Source :- http://www.articlesengine.com/Article/Tips-to-resolve--Extent-E-ID-in-database-ID-DB-ID----/518598/1

How to Resolve “The text, ntext, or image node...” Error

The B-tree metadata structure of a SQL Server database saves the table records in various nodes, which are linked to each other in a hierarchical order. These nodes are primarily of three types – root node, branch node, and leaf node. The root node includes the value of branch nodes, which further contains values of leaf nodes. However, there might be certain situations where the child node does not match with the parent node. This situation mainly occurs when your SQL Server database gets corrupted/damaged. In such cases, you receive an error message while accessing the records saved in the SQL Server table. To access the table records after SQL database damage, restore the data from an updated backup. However, if no backup is available or backup does not fulfill your requirement, then you need to repair the table using a SQL Database Recovery software.

The above scenario is explained using the below error message:

“Table error: Object ID O_ID. The text, ntext, or image node at page P_ID1, slot S_ID1, text ID TEXT_ID does not match its reference from page P_ID2, slot S_ID2.”

The error message appears when there the child node does not match its parent node. This occurs when:

The timestamp of the parent node is different from the timestamp of the child node.
The child node consists of text data, which is greater than the size of parent node.

The above scenarios primarily occur when your SQL Server database gets logically or physically damaged. To overcome the above error message and to access the table records, you need to repair the database. To do so, below are the two ways:

A physically damaged database requires replacement of damaged system component.
A logically corrupted database can be repaired using an inbuilt repair command – DBCC CHECKDB. The statement should be executed with a correct repair clause. In case logical corruption persists after running the above repair command, then you need to search for an effective MS SQL Recovery application that can repair your database. To find a powerful SQL Database Repair tool, search the Internet.

SQL Recovery is one powerful tool that successfully repairs logically corrupted database created in SQL Server 2008, 20005, and 2000. The repaired database is in re-usable state and can be stored at required destination. The SQL Database Repair application supports Windows 7, Vista, 2008, XP, 2003, and 2000.

Storing SQL Server Database on Network Server May Cause Corruption

Microsoft usually urges that you should use a SAN (Storage Area Network) or locally attached hard drive to store MS SQL Server database (MDF) files as this configuration improves SQL Server reliability and performance. Use of network database (stored on network server or NAS [Network Attached Server]) is disabled by default in Microsoft SQL Server. In order to ensure complete data safety and to prevent need of SQL Database Recovery, you should not use SQL Server database on networked server.

Though, it is not recommended, but it is possible to configure MS SQL Server for storing the database on a NAS storage server or networked server. Data storage servers, that are employed to store MDF database, must meet the SQL Server needs for 'write-through guarantees' and 'data write ordering'.

If you try to create SQL Server database file on UNC or mapped network location results into any of the below error message:

5105 "Device Activation Error"

Or

5110 "File 'file_name' is on a network device not supported for database files."

The above behavior is quite expected. The trace flag 1807 let you to bypass the check process and enables you to configure MS SQL Server with the networked database files. Microsoft SQL Server and various other relational database management systems use the transaction log and associated recovery logic, that ensure database integrity and consistency in case of system failure or improper system shutdown.

These SQL Server Recovery protocols bank on the capability to write instantly on the hard drive media so that if any operating system I/O (Input/Output) write call returns to database manage, recovery system is assured that write operation is really complete or the completion of write process can be ensured. Any type of failure by any hardware or software components of this protocol may result in complete or partial data loss due to corruption of MDF file.

In such critical circumstances, you need to restore the damaged database from the most recent backup. However, if the backup is either not available or not updated, third-party MS SQL Recovery software is the only way to go.

The MS SQL Recovery applications are powerful enough to carry out in-depth scan of entire SQL Server database and extract all inaccessible database objects. With the read-only and simple graphical user interface, they are completely safe and easy to use.

SQL Recovery is the most prominent tool that enables absolute recovery in all cases of MDF corruption. It restores all database objects such as tables, reports, forms, modules, queries, constraints, stored procedures, and triggers. The software is compatible with MS SQL Server 2008, 2005, and 2000.

Fixing “Multiple IAM pages for object....” Error Message

MS SQL Server database data files are divided into numerous GAM (Global Allocation Map) intervals. Each GAM interval consists of at least 4GB space or 64,000 extents. The page interval allocates one or more pages to an index that contains an IAM (Index Allocation Map) page for that GAM interval. However, the problem occurs when the index contains more than one IAM page for a GAM interval. The above situation primarily occurs when the SQL Server database gets corrupted or damaged. To overcome such database corruption scenarios, an easy way followed by most of the database users is restoration from an updated backup. However, in case of unavailability of a valid backup, you need to use advanced MS SQL Recovery application.

Consider a practical case, where you attempt to access the records saved in one of your tables. Instead of viewing the table records, you receive the below error message:

“Table error: Multiple IAM pages for object ID O_ID, index ID I_ID contain allocations for the same interval. IAM pages P_ID1 and P_ID2.”

The same error message pops up every time you attempt to access the database table records. Additionally, the records saved in the database table become inaccessible after the above error message pops up.

Cause:

The reason for the occurrence of the above error message is that there are two IAM pages (P_ID1 and P_ID2) that cover the same GAM interval. This occurs either due to physical or logical corruption of the database.

Resolution:

To resolve the above error message and to access the database table records, you need to repair the database using below methods:
Change the damaged/corrupt system component to isolate the physical damage reasons.
Run DBCC CHECKDB command (with repair clause) to isolate all logical corruption scenarios.
If the problem persists, then you need to repair the database using an advanced SQL Database Recovery software. A SQL Repair application repairs corrupt database in all cases of logical corruption.

Stellar Phoenix SQL Recovery is a trust-worthy solution for resolving all sorts of corruptions in SQL database. The software brings the database in a re-usable state in three simple clicks. Compatible with SQL Server 2008, 2005, and 2000, the SQL Repair tool saves the repaired database at required location. It is designed for Windows 7, Vista, 2008, XP, 2003, and 2000.

Source Taken From :-
http://www.articlesengine.com/Article/Fixing--Multiple-IAM-pages-for-object------Error-Message/514071/1