SQL Server Error Msg 2570 - type "In-row data". Column value is out of range for data type

Who Should Read This Blog: MSSQL Server DBA who needs to get detailed knowledge about resolving DB corruption.

Walk through following blog which deals with from corrupt DB restore to fixing the DB corruption.

Here I am trying to restore a databse for which backup is stored in D:\TuitionaffordableDBs\TuitionafordableDBBackups folder.
Before giving restore command we should always verify the backup as bellow-

RESTORE VERIFYONLY
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

I got the following error msg as a result of this command. This will be a good learning for you.

"Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details."

The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf" is not in a valid directory.
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.

Let's Analyze: It seems that there are some storage problem in C: drive. But there was enough space in C: Drive for this backup when I checked.
Means??? there are some other isse which I need to address.

I tried with below restore command to get the actual error-

RESTORE DATABASE TuitionAffordable1
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

Now get the bellow error msg-

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'TuitionAffordable1' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'TuitionAffordable1_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Let's Analyze: The path "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA" is missing where new .mdf needs to be created. So I Created the path as shown in the error and executed 'VERIFYONLY' once again.

The backup set on file 1 is valid. --The backup file is valid.

Custom Search

Try now-

RESTORE DATABASE TuitionAffordable1
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

And the DB got restored perfectly. Let's walk through this DB and find the corruption.

DBCC CHECKDB (TuitionAffordable1)

Below is the error-
Msg 2570, Level 16, State 3, Line 1
Page (1:24433), slot 91 in object ID 421544440, index ID 1, partition ID 72053332339697408, alloc unit ID 12127594044809216 (type "In-row data"). Column "AvgVal" value is out of range for data type "float". Update column to a legal value.

Let's Analyze: Find out the object name for the object ID present in the error msg-
SELECT OBJECT_NAME(421544440)

Got the object name as "Salary". Go ahead as following-

SELECT * FROM Salary

It also gave an error-
An error occurred while executing batch. Error message is: Arithmetic Overflow.

Which means there is some problem in data type of column AvgVal of Table Salary. Change the data type to DECIMAL.

ALTER TABLE PRODUCTS
ALTER COLUMN PRICE DECIMAL (10,5)

Problem is resolved

Trying another method to fix the corruption

DROP DATABASE TuitionAffordable1

Msg 3702, Level 16, State 3, Line 1
Cannot drop database "TuitionAffordable1" because it is currently in use.

--Any repair statement needs DB to be in single user mode.

Change the connection to master

DBCC CHECKTABLE (PRODUCTS, REPAIR_ALLOW_DATA_LOSS)

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

I change the database in single user mode-
ALTER DATABASE Tuitionaffordable1
SET SINGLE_USER WITH NO_WAIT

And then try to restore the DB but again it throws the same error of arithmetic overflow. So here we determined that repairing DBCC CHECKTABLE (PRODUCTS, REPAIR_ALLOW_DATA_LOSS) sometime is not the last resort.
We should always analyse the error before proceeding.

Regards,
http://tuitionaffordable.webstarts.com