February 9, 2024 at 5:17 pm
We have a large table that stores file data in a column with an Image data type. One of the records appears to have corrupt data. When trying to select the record with the file data, I get the following error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
If I leave out the Image column when I select, it works.
The record is old and no longer needed, so I tried to delete it and got this error:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xf261f234; actual: 0xd8f1ee87). It occurred during a read of page (1:1687725) in database ID 5 at offset 0x0000033815a000 in file 'D:\Data\xxxx.mdf'. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I am also unable to set the Image column to null. Is there some way I can remove this record? It is an old record, so any database backups we still have would have the same issue.
February 9, 2024 at 5:44 pm
Have you tried running an integrity check on that database - or DBCC CHECKTABLE for that table? That would be the first thing I would do to verify there isn't additional integrity issues in the database and/or table.
If it is just this one row in the table (or just a few rows) - I would consider creating a new table and copying from the existing into the new table. I could then either ignore the bad rows or force a NULL for that column during the copy. Once the data has been copied, rename the existing table with _old and rename the new table to the existing table.
Before I did that - I would follow up and make sure we could change the column data type from the deprecated image data type to a supported data type.
To fix this you may need to run a repair with data loss - which is a last resort and would almost certainly eliminate the rows that have an issue. If you capture the data before doing that it would be possible to recreate the row(s) and add them after the repair. But again, that is a last resort option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 12, 2024 at 1:44 pm
I did run dbcc checkable and it showed 8 consistency errors and says repair_allow_data_loss is the minimum repair level for the errors found. Not surprising. I am not willing to do that. It looks like I will have to copy the data to a new table as you suggested.
Thanks for the help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply