Geeks With Blogs

News
Welcome to my blog.
Here's what we've got on the menu today:

Lorin Thwaits A geek says what?

Below is a question that was posted to AZGroups earlier this afternoon.  I removed the database and table names because I'm downright anal about security :)

Having table problems, and I am just stumped....  Our dev website started coming back with the following error when a user logs in:

Microsoft OLE DB Provider for SQL Server error '80004005'
Warning: Fatal error 605 occurred ....

Microsoft's MSDN gives:

ERROR_BAD_COMPRESSION_BUFFER 605
The specified buffer contains ill-formed data.
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes__500-999_.asp)

I am no DBA by any means but I know enough that I with this error I should probably check the database for issues.  So, I run a DBCC
CHECKDB("MyDatabase"), which showed the errors:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1662785131, index ID 0: Page (1:3178591) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1664882283, index ID 0, page (1:3178591). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 6 and 2097152.

DBCC results for '<< table name >>'.
There are 91133 rows in 91135 pages for object '<< table name >>'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '<< table name >>' (object ID 1662785131).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1664882283)' (object ID 1664882283).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (<< database name >>.dbo.<< table name >> ).

First off, I fully admit I have not much of a clue what the problem is, but I am pretty certain I have one.  No other tables in the database are having issues, and since this is a development SQL Server, I thought I would try a drop and remake the table and re-import the data.  After doing a full DROP TABLE, then CREATE TABLE, I re-imported the data from our AS/400, which processed without issue.  However, I am still getting the above error.

And to make matters even stranger...  The stored procedure that is run from the web page during the login process, if I run it from Query Analyzer I get no error, but run from the web page it errors every time with the error given above.

First question: What (in plain english) is this error?
2nd question: I am near 100% certain it is not the data coming from the 400, so what would cause this error?
3rd question:  How can I fix it?
4th question:  And why does it error only on the web but not in QA?

Possible answer for why it shows up in an ADO connection but not in QA: Different connections will have different characteristics depending on their connection string.  It is possible that the execution plan would differ between these connections.  Or is there any chance you're passing different IDs into the sproc?  Run Profiler to see exactly what queries are being presented to the SQL server from the web server, and then run that exactly in QA.

In line with Q#3, I looked up "repair_allow_data_loss" in BOL and it states I must run the database in single user mode, but I don't see how this is done.  But I have to wonder if this is even the real solution if after recreating the table and importing records I am back with the same error.

Answer for Single User Mode: To start SQL in single-user mode, you have to stop the MSSqlServer service, and start it using the -m switch. This can be done from the command prompt like this:
net stop mssqlserver
net start mssqlserver -m

After backing up the database from the test server and restoring on my machine, in Query Analyzer when I run the query for logging in, I get the following error:

Server: Msg 605, Level 21, State 1, Procedure fnDocStat, Line 11 Attempt to fetch logical page (1:3178591) in database '<< database name >>' belongs to object '1664882283', not to object '<< table name >>'.  Connection Broken.

I recreated the database on a separate SQL Server and everything runs fine.  But I would still like to know what the deal is here so I can fix it, and that it doesn't happen again in another database.

Thanks.

Answer for everything else: You've got low-level issues in your MDF or NDF.  Basically each 8K chunk in these files is called a "page", and in the header for each of these pages (among other things) is an ID that says what table or index it belongs to.  For some reason there's a page that says it belongs to the table listed as << table name >> above, but in reality it points back to what is either an index or part of the heap.  Check out this excellent in-depth article on SQL IO for more info:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

How to fix it
If you care about having the exact same data then hopefully you have an older backup that works and backups of all logs since then.  In this case you can back up the current transaction log, restore the old full backup, and then restore all the old transaction logs in sequence up to the current log to get the data exactly to the point that it is in now.  Use "WITH NORECOVERY" with all prior logs, and "WITH RECOVERY" for the most recent log.  Enterprise Manager can handle all that for you if you still have a record of your backup jobs in the msdb database.

If you take the backup/restore approach then *after* restoring everything I recommend dropping all the indexes you've got on the database and recreating them.  It could fix the specific thing that's hosed.  You can easily recreate the indexes by making a quick script of what they are now from the Enterprise Manager thing that scripts out a database.

In terms of what caused it, have you upgraded the RAM on the test server recently?  This could come up from having bad RAM.  Also is the test box set up with a RAID controller?  If so then does it have a write cache?  Turning off the write cache can avoid some oddball issues like this.  Finally, had the system lost power at some point?  Extremely rare, but it could have written the index but not the leaf node or heap related to the page in question.  In that case the troublesome page would get pointed to, but would not hold the right data.

This is altogether a very rare issue, and I hope you can get to the bottom of it.

Posted on Wednesday, May 10, 2006 1:14 PM SQL , Errors | Back to top


Comments on this post: What to do when SQL error 605 or 823 strikes

# re: What to do when SQL error 605 or 823 strikes
Requesting Gravatar...
I have had a similar problem with the Msdb database. Running the DBCC checkdb and then DBCC checkalloc both with the option "REPAIR_ALOW_DATA_LOSS" solved the problem. :-)
Left by IceSat on Oct 12, 2006 10:29 PM

# re: What to do when SQL error 605 or 823 strikes
Requesting Gravatar...
Thanks - hopefully the key to my problem is in your post. I'll certainly give it a try, it's driving me crazy!
Left by Zhu Zhu on Oct 23, 2009 1:31 AM

# re: What to do when SQL error 605 or 823 strikes
Requesting Gravatar...
Great read. I enjoyed reading your post and I like your take on the issue. I always get the same errors. Thanks.
Left by dog tags on Dec 27, 2009 5:24 AM

Your comment:
 (will show your gravatar)


Copyright © Lorin Thwaits | Powered by: GeeksWithBlogs.net