Operating system returned error 23

Question

Operating system returned error 23

sanjaydut26

SSCommitted

Points: 1968
More actions
September 20, 2016 at 7:03 am

#329896

Hello, Everyone
I am facing this issue when running DBCC checkDB
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x0000000006e000 in file 'D:\HCLive DB\HCLiveV3Fresh.mdf:MSSQL_DBCC10'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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 trying to take the backup of the DB and It is throwing a error message. Then i tried to run DBCC checkDB and i got the error message shown above.
1. What is causing this error?
2. How do i take the backup of that DB?
3. Can i create DB by copying Mdf and Ldf files of that DB to a different location using
create DATABASE [DB_Name]
ON
( FILENAME = ''C:File.mdf')
LOG ON ( FILENAME = 'C:File.ldf')
for attach;
GO
Thanks in Advamce,
Sanjay
September 21, 2016 at 11:38 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sanjay

Have you done what it suggests in the error message? Looks as if the problem is so serious that you can't back up the database. Copies of the mdf and ldf are unlikely to be any good, either. Do you have a backup you can restore from, in the likely event that the database is corrupted beyond repair?

John

sanjaydut26 SSCommitted Points: 1968 More actions · Answer 2

Hey John,

It's a old DB which was not in use. I don't have any backups.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

In which case, it's likely you'll need to accept some data loss to get this fixed. But we can't be sure of that until you do what it suggests in the error message.

John

sjimmo SSChampion Points: 11139 More actions · Answer 4

sanjaydut26 (9/20/2016)
Hello, Everyone
I am facing this issue when running DBCC checkDB
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x0000000006e000 in file 'D:\HCLive DB\HCLiveV3Fresh.mdf:MSSQL_DBCC10'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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 trying to take the backup of the DB and It is throwing a error message. Then i tried to run DBCC checkDB and i got the error message shown above.

Sanjay, you do not say what version of SQL you are using. But MS does acknowledge a bug in 2008 which will give this error. Their work-around is to use TABLOCK with your DBCC.

Have you made sure that your tempdb has enough space? If there isn't then you can get this error. By enough space, you should have at a minimum 1 1/2 times the size of your largest table with indexes. If you can make it bigger than do so. You can always shrink it back if you choose to do so later. DBCC's heavily use tempdb.

After that, if tempdb is good then the 823 error tells me to look ad the storage system. You do not say if this is a SAN or a hard drive. It may be that there is a bad spot on the hard drive and SS can no longer read the data there. In this case you will see corruption in your DB.

Look at the suspect_pages table in msdb and see what errors are being reported for that DB (dbid). You can see the page for the error and the count. You will probably have to run a dbcc checkdb with repair_allow_data_loss in order to fix the DB.

You can also, before doing that, have your sysadmin run a chkdsk on the drives with your database. SS will need to be down in order to do this.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan