error 823

Hello all,
I am fighting this error for a month now.I will explain:It started with the followying steps:

1 Could not allocate space for object '(SYSTEM table id: -789977678)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full..Error: 1105, Severity: 17, State: 2

2 SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
Error: 3624, Severity: 20, State: 1.

3 I/O error (bad page ID) detected during read at offset 0x000000bf074000 in file 'f:\SQL data files\data files\dw_prod.mdf'..
Error: 823, Severity: 24, State: 2

4 DBCC CHECKDB (dw_prod) executed by sa found 7 errors and repaired 0 errors.

5 DBCC CHECKDB (dw_prod, repair_rebuild) executed by sa found 7 errors and repaired 0 errors.

6 DBCC CHECKDB (dw_prod, repair_allow_data_loss) executed by sa found 7 errors and repaired 7 errors.

-- AFTER DBCC CHECKB--therefore in reality the error wasnot corrected--

7 I/O error (bad page ID) detected during read at offset 0x00000091d3a000 in file 'f:\SQL data files\data files\dw_prod.mdf'..
Error: 823, Severity: 24, State: 2

8 SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
Error: 3624, Severity: 20, State: 1.

9 I/O error (bad page ID) detected during read at offset 0x00000091d36000 in file 'f:\SQL data files\data files\dw_prod.mdf'..

10 DBCC CHECKDB (dw_prod, repair_allow_data_loss) executed by sa found 24 errors and repaired 24 errors.

11 I/O error (bad page ID) detected during read at offset 0x000000f6994000 in file 'f:\SQL data files\data files\dw_prod.mdf'..
Error: 823, Severity: 24, State: 2

12 I finally had no other solution than to recover from a good backup (100 G database) - BUT my network admin considers that his hardware is well and WE DID NOT CHECK THE damn HARDWARE!!!!

13 24 H after again I/O error (bad page ID) detected during read at offset 0x0000009d456000 in file 'f:\SQL data files\data files\dw_prod.mdf'..

14 another recovery OK

15

1313 Could not allocate space for object '(SYSTEM table id: -219347948)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full..

14 I/O error (bad page ID) detected during read at offset 0x000000440d0000 in file 'f:\SQL data files\data files\dw_prod.mdf'..

15 dbcc checkdb finds nothing

My question is:

- what I am going to do next? I have recovered now, all went well till the tempdb was not running out of space... I am in the process of checking the hardware

- please say what you think about this.I have never experienced such a pain!

Thanks

LMT|||I had the infamous 823 errors not long ago. In our case, it was a bad raid controller. Poke through your System event logs for error number 50 or 55. Thes can mean some disk problem. Unfortunately, some disk problem can be anything from the mainboard up to the spindle of the disk itself. Since the only moving part is the disk, you should start there, and do a full disk check for bad sectors and such. There are usually vendor specific utilities that should be used for that, so I am afraid I can not help you much further than that. Good luck.|||thanks

is it possible that this 832error to be determined partly bi a tempdb running out of space??

in my case most of errors came after the temdb ran out of space

lmt|||is tempdb also sitting on f: drive?

check your system log in event viewer. that's a darn good place to start. keep a log of dates and times from sql errorlog in front of you while going through system log and see if there were any io subsystem-related errors.

also, put tempdb onto a different drive. always a good idea to keep that bad boy separate. raid-0 for this guy is more than enough. i'd put it on a separate controller as well, preferrably a faster one.