Thursday, March 8, 2012

dbcc error on sysobjects

Hi,
I am getting error 8970 when I run a dbcc on my production database. Please
help.
Error Details:
--
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
was created NOT NULL, but is NULL in the row.
DBCC results for 'sysobjects'.
There are 590 rows in 11 pages for object 'sysobjects'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
tiaTake a backup of the database. Restore on another server. Run dbcc checkdb
against the restored database. If it still errors out, run the
repair_rebuild option
and if it still does not work, you may have to run the
REPAIR_ALLOW_DATA_LOSS
See what happens. If everything looks good on this restored database on the
other server after performing the fix, then run it on production. if things
go south, atleast you have a backup of the database restored someplace else.
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database.
> Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column
> 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>|||This is a multi-part message in MIME format.
--=_NextPart_000_008A_01C60D27.01D7E6D0
Content-Type: text/plain;
format=flowed;
charset="Utf-8";
reply-type=original
Content-Transfer-Encoding: 8bit
Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
Also, see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
And, this is what Books Online has to say about that error:
Error 8970
Severity Level 16
Message Text
Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
NULL, but is NULL in the row.
Explanation
A column was created as not NULL, but is NULL in the row.
Per-state information:
State 1: The column is a regular data column.
State 2: The column is a SqlVariant and the data contained within it is NULL.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
and application logs and the SQL Serverâ?¢ error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
the record is deleted and all indexes are rebuilt.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sc_simsl" <sc_simsl@.hotmail.com> wrote in message
news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> Hi,
> I am getting error 8970 when I run a dbcc on my production database. Please
> help.
> Error Details:
> --
> Server: Msg 8970, Level 16, State 1, Line 1
> Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
> was created NOT NULL, but is NULL in the row.
> DBCC results for 'sysobjects'.
> There are 590 rows in 11 pages for object 'sysobjects'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> tia
>
--=_NextPart_000_008A_01C60D27.01D7E6D0
Content-Type: image/gif;
name="caution.gif"
Content-Transfer-Encoding: base64
Content-ID: <008601c60d1e$a0107190$0300a8c0@.tibwork>
R0lGODlhDAALAPcAAAAAADMAAGYAAJkAAMwAAP8AAAAzADMzAGYzAJkzAMwzAP8zAABmADNmAGZm
AJlmAMxmAP9mAACZADOZAGaZAJmZAMyZAP+ZAADMADPMAGbMAJnMAMzMAP/MAAD/ADP/AGb/AJn/
AMz/AP//AAAAMzMAM2YAM5kAM8wAM/8AMwAzMzMzM2YzM5kzM8wzM/8zMwBmMzNmM2ZmM5lmM8xm
M/9mMwCZMzOZM2aZM5mZM8yZM/+ZMwDMMzPMM2bMM5nMM8zMM//MMwD/MzP/M2b/M5n/M8z/M///
MwAAZjMAZmYAZpkAZswAZv8AZgAzZjMzZmYzZpkzZswzZv8zZgBmZjNmZmZmZplmZsxmZv9mZgCZ
ZjOZZmaZZpmZZsyZZv+ZZgDMZjPMZmbMZpnMZszMZv/MZgD/ZjP/Zmb/Zpn/Zsz/Zv//ZgAAmTMA
mWYAmZkAmcwAmf8AmQAzmTMzmWYzmZkzmcwzmf8zmQBmmTNmmWZmmZlmmcxmmf9mmQCZmTOZmWaZ
mZmZmcyZmf+ZmQDMmTPMmWbMmZnMmczMmf/MmQD/mTP/mWb/mZn/mcz/mf//mQAAzDMAzGYAzJkA
zMwAzP8AzAAzzDMzzGYzzJkzzMwzzP8zzABmzDNmzGZmzJlmzMxmzP9mzACZzDOZzGaZzJmZzMyZ
zP+ZzADMzDPMzGbMzJnMzMzMzP/MzAD/zDP/zGb/zJn/zMz/zP//zAAA/zMA/2YA/5kA/8wA//8A
/wAz/zMz/2Yz/5kz/8wz//8z/wBm/zNm/2Zm/5lm/8xm//9m/wCZ/zOZ/2aZ/5mZ/8yZ//+Z/wDM
/zPM/2bM/5nM/8zM///M/wD//zP//2b//5n//8z//////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAKwALAAAAAAMAAsA
QAgiAFkJHEiwoMGDrAooRJhwIUKFBRhCZEhR4MSHDitijDgwIAA7
--=_NextPart_000_008A_01C60D27.01D7E6D0--|||Thanks all for your suggestions.
I had tried the REPAIR_ALLOW_DATA_LOSS but since it's sysobjects that's
corrupt, it does not proceed with the dbcc.
Since it's a production database and I cannot go back to an older backup, I
was forced to update the system table.
Updated the column in sysobjects, ran dbcc again. Gave the same error on
another column in the same row, repeated the steps a couple of times.
The table is clean now and dbcc checkdb/checkcatalog too do not give any
errors. Also tried accessing the object and it works fine.
I know the MS guys are going to scream, but this was my last resort!
thanks.
"Tibor Karaszi" wrote:
> Object id 1 is the sysobjects table. Usually, DBCC CHECKDB will tell you the needed repair level to
> repair a corruption. If it specified REPAIR_ALLOW_DATA_LOSS, then the corrupt data will be removed.
> In this case, it might mean that the offending rows from sysobjects has to be removed, hence, the
> tables that they "point to" also will be removed. Perhaps someone from MS will jump in and comment
> on that. Consider opening a case with MS if reverting to your latest clean backup isn't an option.
> Also, see http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> And, this is what Books Online has to say about that error:
> Error 8970
> Severity Level 16
> Message Text
> Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT
> NULL, but is NULL in the row.
> Explanation
> A column was created as not NULL, but is NULL in the row.
> Per-state information:
> State 1: The column is a regular data column.
> State 2: The column is a SqlVariant and the data contained within it is NULL.
> Action
> HARDWARE FAILURE
> Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
> and application logs and the SQL Serverâ?¢ error log to see if the error occurred as the result of
> hardware failure. Fix any hardware related problems.
> If you have persistent data corruption problems, try to swap out different hardware components to
> isolate the problem. Check to ensure that your system does not have write caching enabled on the
> disk controller. If you suspect this to be the problem, contact your hardware vendor.
> Finally, you might find it beneficial to switch to a completely new hardware system, including
> reformatting the disk drives and reinstalling the operating system.
> RESTORE FROM BACKUP
> If the problem is not hardware related and a known clean backup is available, restore the database
> from the backup.
> DBCC CHECKDB
> If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
> extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
> CHECKDB with the appropriate repair clause to repair the corruption.
>
> Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
> your primary support provider before executing this statement.
>
> If the record is from a non-clustered index, performing a repair will rebuild the index. Otherwise,
> the record is deleted and all indexes are rebuilt.
>
> Caution This repair may cause data loss.
>
> If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
> primary support provider.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "sc_simsl" <sc_simsl@.hotmail.com> wrote in message
> news:90D4B6D2-171D-47BB-8A94-74475992D324@.microsoft.com...
> > Hi,
> > I am getting error 8970 when I run a dbcc on my production database. Please
> > help.
> >
> > Error Details:
> > --
> > Server: Msg 8970, Level 16, State 1, Line 1
> > Row error: Object ID 1, index ID 0, page ID (1:92), row ID 67. Column 'uid'
> > was created NOT NULL, but is NULL in the row.
> > DBCC results for 'sysobjects'.
> > There are 590 rows in 11 pages for object 'sysobjects'.
> > CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> > 'sysobjects' (object ID 1).
> >
> > tia
> >
>

No comments:

Post a Comment