Tuesday, March 23, 2010

SQL Server Database Corruption If You Run a Query with Parallel Execution Plan

You may come across Access Violation if you run an SQL query, which uses parallel execution plan in Microsoft SQL Server 2008. Access violation is an error condition, which may occur while performing general operations on the SQL database. It may occur due to application malfunction, software bug, or database corruption. It renders your database inaccessible and may cause data loss, if you don't have a complete backup to restore data from. To overcome such issues, SQL Database Recovery is required.

As an example of Access Violation in Microsoft SQL Server 2008, consider the below scenario:

In MS SQL Server 2008, you're running a query that includes all the below mentioned clauses:
Inner join
Union all
Group by
The database query is using parallel execution plan.
You execute the query.

In the above scenario, you may face access violation. And, you receive the following 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.”

Furthermore, the mini-dump file is returned under the log folder of MS SQL Server 2008.

Cause

As sated in the above error message, this problem may have occurred due to either database corruption or a software bug. After corruption, SQL Server can not read the database and this behavior occurs.

Resolution

Run DBCC CHECKDB utility to try fixing database corruption. If the database is severely corrupted, it may not work. In such cases, third-party SQL Server Recovery applications are worth using.

The MS SQL Recovery applications are particularly designed to thoroughly scan whole SQL Server database and extract all damaged and inaccessible objects from it. They allow you to perform SQL Recovery on your own as they have simple and rich graphical user interface.

SQL Recovery software safely and successfully repairs damaged SQL Server database in all cases of corruption. It works well with Microsoft SQL Server 2008, 2005, and 2000. The software restores all database objects such as tables, reports, forms, macros, and more.

No comments:

Post a Comment