October 28, 2003 at 9:04 am
I knew I solved , but I did not!
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
13 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
October 28, 2003 at 11:05 am
Look at filegrowth options of database.
Try to switch to Recovery Model: Simple
Search for exotic query, which may automaticaly use tempdb.
October 28, 2003 at 12:07 pm
Do you have free spaces in the drive your tempdb resides when this error occurs? Has TempDB been set to auto growth?
823 Error messages usually imply on disk corruption most typically arising from problems at a level below the operating system (e.g. Hardware component failure, bad/outdated device driver). DBCC CheckDB is recommended to perform which you already did.
Do you see any other error messages in SQL Server errorlog?
October 29, 2003 at 6:31 am
hi!
and don't forget to check the windows *System* event log for reported hardware timeouts, driver errors or similar (eg. on most SCSI adapters you get something like "timeout exceeded accessing rdisk(x)\partitions(y)") which will indicate a hardware problem.
best regards,
chris.
October 29, 2003 at 1:53 pm
sorry for my late reply.
I restored into a different PC and even when I reproduced the tempdb error I did not get the 823 err.
I will look into SQL better.
If my 823 appear with no tempdb then I will be 100% it is a hardware.
Thanks
October 29, 2003 at 1:54 pm
and no error message at the PC app level or system level...wierd!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply