Day 30 More Fatal Errors and BUGs

1. For sql server 2000 and earlier you may get 5242 and 5243 corruption in the record  structure(for sql server 2005 onwards you may get 824).
   

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database ‘MyCorruptDatabase’(ID:12) on page (1:34923). Please contact technical support. Reference number 4.

http://sqlskills.com/BLOGS/PAUL/post/Corruption-errors-Msg-5242-Level-22.aspx

1. For sql server 2000 you can change the system objects (allow updates) which is not so easy in sql server 2005(not possible), so if you make the changes in system object and restore the database on sql server 2005, you may get below error.
   

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (object_id=xxx) of row (object_id=x,column_id=y) in sys.columns does not have a matching row (object_id=xxx) in sys.objects.

*Paul Randal has shown us how to correct these kind of problem, but PLEASE NO ONE SHOULD TRY THIS IN PRODUCTION.

http://sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx

1. Corrupt database, try DBCC CheckDB with TABLOCK to get exact issue, as TABLOCK will not create snapshot.
   

Msg 8967, Level 16, State 216, Line 1

An internal error occured in DBCC which prevented further processing. Please contact Product Support.

http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-bug-that-people-are-hitting-Msg-8967-Level-16-State-216.aspx

1. SQL Server 2005 9.0.3161 on Win 2k3 R2
   

Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

This issue appears to be related to the fact database compatibility is set to 80.  I have filed a product bug for tracking purpose

Setting database compatibility to be 90 resolved the issue

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/59c0e1e0-741d-48c9-9eaf-38b5cc1f57d3/

1. Delete primary key having more than 253 foreign key – BUG .
   

When you have foreign keys more the 253 and if you want to delete record, you will get below error:

Msg 8621, Level 17, State 2, Line 1  The query processor ran out of stack space during query optimization. Please simplify the query

https://connect.microsoft.com/SQLServer/feedback/details/172561/the-query-processor-ran-out-of-stack-space-during-query-optimization-during-simple-delete

It’s a BUG in sql server

1. DBCC DBInfo bug
   

Msg 407, Level 16, State 1, Line 1

Internal error. The string routine in file e:\sql10_main_t\sql\common\dk\base\src\dstreambase.cpp, line 269 failed with HRESULT 0x8007007a.

The complete list of connect – BUGs can be found here:

https://connect.microsoft.com/SQLServer/SearchResults.aspx?SearchQuery=sql+server

1. On LOB data, PFS page looks full but it’s not, it’s a BUG on sql server 2005 SP2 and fixed in later version.
   

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:xxx) in object ID xxx, index ID 1, partition ID x, alloc unit ID xxxx (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL

1. Another Bug in sql server 2005 SP2 when you run DBCC DBREindex (for 2005 it is ALTER INDEX).
   

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID x, index ID 1, partition ID xx, alloc unit ID xxx (type In-row data). Keys out of order on page (1:xxxxx), slots x and x.

To overcome this run ALTER INDEX instead of DBCC REINDEX .

http://sqlskills.com/BLOGS/PAUL/post/Corruption-bug-that-people-are-hitting-Msg-2511-using-DBCC-DBREINDEX.aspx