July 15, 2013 at 4:32 am
Hi
I am trying to find the name of a table in our database given a objectID, when i run this
select *
from sysobjects
where id = 632864
I get no records returned. any ideas?
I have got the objectID from
Table error: Object ID 632864, index ID 17051, page ID (1:28510200). The PageId in the page header = (55702:800)
Thanks
July 15, 2013 at 4:47 am
The object ID is only unique within each database. Are you sure you're querying the correct database? By the way, sysobjects is only included for backward compatibility. You should use sys.objects instead.
John
July 15, 2013 at 4:57 am
You've got database corruption.
The table name will be printed as part of the CheckDB output. Please run the following and post the full and complete output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2013 at 4:59 am
select OBJECT_NAME(632864)
---------------------------------------------------------------------
July 15, 2013 at 5:08 am
george sibbald (7/15/2013)
select OBJECT_NAME(632864)
Sorry that did not work, it came back with NULL
July 15, 2013 at 5:09 am
GilaMonster (7/15/2013)
You've got database corruption.The table name will be printed as part of the CheckDB output. Please run the following and post the full and complete output.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Sorry i forgot to mention i am using SQL 2000. so sys.objects is not valid. Yes i am querying the correct DB
July 15, 2013 at 5:11 am
2000 questions should go in the SQL 2000 forums.
You have database corruption, so that object id is very likely invalid, especially given the nonsensical index id listed (max index id is 255)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2013 at 5:28 am
GilaMonster (7/15/2013)
2000 questions should go in the SQL 2000 forums.You have database corruption, so that object id is very likely invalid, especially given the nonsensical index id listed (max index id is 255)
Im sorry for posting in the incorrect forum :crying:
I am aware i have database corruption, i am trying to script all the objects that are good and leave all the corrupted tables (i am aware that i will lose data). So i was trying to find out where this corrupted table is.
That line i posted above is from the CHECKDB i ran earlier, i will post the complete output in a moment
July 15, 2013 at 5:29 am
imran.adam (7/15/2013)
george sibbald (7/15/2013)
select OBJECT_NAME(632864)Sorry that did not work, it came back with NULL
it would work for a valid id. Just wanted to show you a quick way to resolve it. It proves that that object_id is not valid in your database. run checkdb as gail suggests
---------------------------------------------------------------------
July 15, 2013 at 5:34 am
check also the minimum repair level checkdb reports.
If its a non-clustered index that turns out to be corrupted you may be able to just drop and recreate it.
Do you have a clean backup? almost always your best way out of a corruption.
---------------------------------------------------------------------
July 15, 2013 at 5:38 am
george sibbald (7/15/2013)
check also the minimum repair level checkdb reports.If its a non-clustered index that turns out to be corrupted you may be able to just drop and recreate it.
Do you have a clean backup? almost always your best way out of a corruption.
Unfortunately no recent clean backup 🙁 and ive been i cannot repair as its seriously corrupt
I dont know if its a non-clustered index or table that is corrupted. My strategy as i stated above was to create a new db and script all the objects that are 'good' and not the 'bad' ones. This is why im trying to ascertain the corrupt objects.
July 15, 2013 at 5:54 am
the checkdb will give you the corrupted object names
---------------------------------------------------------------------
July 15, 2013 at 5:56 am
OK, here is the output from the CHECKDB (i cannot see the objectname?)
Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:28502112) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -25000, index ID 0, page ID (1:28502112). The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -25000, index ID 0, page ID (1:28502112). The PageId in the page header = (0:0).
CHECKDB found 1 allocation errors and 1 consistency errors in table '(Object ID -25000)' (object ID -25000).
Msg 8921, Level 16, State 1, Line 1
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 10 pages from (1:28502112) to (1:28510199). See other errors for cause.
Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 10 pages from (1:28510200) to (1:28518287). See other errors for cause.
Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 10 pages from (1:28518288) to (1:28526375). See other errors for cause.
CHECKDB found 3 allocation errors and 0 consistency errors not associated with any single object.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 632864, index ID 17051, page ID (1:28510200). The PageId in the page header = (55702:800).
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 632864)' (object ID 632864).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 134676585, index ID 1, page ID (1:28518288). The PageId in the page header = (26880:131072).
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 134676585)' (object ID 134676585).
CHECKDB found 6 allocation errors and 1 consistency errors in database 'Bon_Live'.
July 15, 2013 at 6:30 am
That is corrupt beyond the point of being able to identify objects. You've got fatal metadata corruption and allocation page corruption.
You're going to be very lucky if you get much at all out of that.
Go through the tables one by one (select name from sysobjects where xtype='U'), see if you can generate the script, see if you can select any of the data. I suspect a good number of objects will fail. It will take quite some time.
May be easier just to go back to the last good backup and accept that everything since is lost.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2013 at 7:07 am
GilaMonster (7/15/2013)
That is corrupt beyond the point of being able to identify objects. You've got fatal metadata corruption and allocation page corruption.You're going to be very lucky if you get much at all out of that.
Go through the tables one by one (select name from sysobjects where xtype='U'), see if you can generate the script, see if you can select any of the data. I suspect a good number of objects will fail. It will take quite some time.
May be easier just to go back to the last good backup and accept that everything since is lost.
Many Thanks Gila!
** NOW JUST NEED THE ADMINS TO MOVE THIS THREAD TO THE SQL 2000 FORUM **
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply