March 2, 2010 at 3:29 am
Hi all,
after a DBCC CHECKDB, I get following messages for some tables in my DB:
Could not find a table or object named 'table'. Check sysobjects.
When I query the sysobjects talbe, I find all the 'missing' tables, with xtype='U'.
I ran the DBCC CHECKTABLE command on the sysobjects but no errors were reported. I can also run the DBCC CHECKTABLE on the 'missing' tables, no errors are reported.
Any ideas ?
Best regards,
Hans
March 2, 2010 at 3:39 am
Please post the full output of the following:
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
March 2, 2010 at 3:44 am
Hi Gail,
here is the output generated by the CHECKDB command:
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_closure'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_fond_detail'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_journal'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_partial_transfer'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_payment'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'cc_payment_3x'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'Install_history'. Check sysobjects.
But I must add that the message 'Query completed with errors.' is printed at the bottom in the status bar ...
March 2, 2010 at 4:07 am
Ok, firstly, this is not a SQL 2005 server, despite the question being in the SQL 2005 forums.
Can you also run DBCC CheckCatalog?
Wouldn't be required on SQL 2005, hence why I didn't ask earlier.
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
March 2, 2010 at 4:52 am
Sorry, my mistake, it is indead a SQL 2000 DB.
Here's the output of the CHECKCATALOG command:
DBCC results for 'm project'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 738101670 (object 'cc_closure') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 754101727 (object 'cc_fond') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 770101784 (object 'cc_fond_detail') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 786101841 (object 'cc_journal') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 802101898 (object 'cc_partial_transfer') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 811149935 (object 'ProductBom') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 818101955 (object 'cc_payment') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 834102012 (object 'cc_payment_3x') does not match between 'SYSOBJECTS' and 'SYSCOLUMNS'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 754101727 (object 'cc_fond') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 770101784 (object 'cc_fond_detail') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 818101955 (object 'cc_payment') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 786101841 (object 'cc_journal') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 811149935 (object 'ProductBom') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
Msg 2513, Level 16, State 15, Line 1
Table error: Object ID 811149935 (object 'ProductBom') does not match between 'SYSREFERENCES' and 'SYSCOLUMNS.'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
March 2, 2010 at 7:39 am
Has someone been messing around with "Allow Updates" and direct updates to the system tables?
Check the objects listed in those errors. Are they tables, stored procs, views, functions?
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
March 2, 2010 at 8:05 am
They are all tables. I'm pretty sure no one as messed with those parameters as the DB is located in a shop where they don't have any SQL tools. Is there a way to be sure 100% ?
March 2, 2010 at 8:52 am
Check the settings of 'allow updates' (sp_configure), check the SQL error log to see if that setting was changed recently.
Don't suppose you know when this started or that you have a clean backup (backup without these errors)?
I think that recreating those tables will fix these errors. SELECT ... INTO to create new tables, add all the constraints and indexes then drop the old tables and rename the new ones.
I'm not particuarly keen in messing with the system tables to fix this, could easily cause more damage than it fixes.
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
March 2, 2010 at 5:29 pm
Either someone's messed with the system tables or there's more output from checkdb. Can you post the complete output of:
dbcc checkdb (yourdbname) with all_errormsgs, no_infomsgs
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 2, 2010 at 10:12 pm
Paul, see 3rd post in this thread. (I can't tell if that's the full and complete output though)
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
March 3, 2010 at 1:27 am
Hi,
this is the output of the sp_configure command:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 0 0
Cross DB Ownership Chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
show advanced options 0 1 0 0
user options 0 32767 0 0
The output I posted of the CHECKDB command is complete. I'm pretty sure no one messed with the system tables.
I don't know when the problem started and I'll have to check with one of my colleagues to see if we have a decent backup or not.
The SELECT INTO solution won't work as I get the message 'Table tablename does not exist' when executing the SELECT command.
I'm wondering if this could be caused by physical problems with the HDD ??
March 3, 2010 at 9:18 am
GilaMonster (3/2/2010)
Paul, see 3rd post in this thread. (I can't tell if that's the full and complete output though)
Yes, saw that while reading the thread - and that's why I'm asking for the full output, just to make sure.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 3, 2010 at 9:20 am
Can you run DBCC CHECKTABLE on sysobjects, sysindexes, syscolumns please?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 4, 2010 at 3:43 am
Hi Paul, Gail,
thanks a lot for all the input. I managed to solve the problem as follows:
as the missing tables were not of that importance, I restored a backup of the corrupt database. Before the restore I put aside the 'corrupt' DB. I took all the data off the missing tables from the backup and put it in a temp DB. Then I restored the 'corrupt' database and dropped all of the missing tables. I recreated them by using the SELECT INTO command and recreated the indexes and FK with the sql manager. And now, all is going well again. Apparently the problem began around 27/02 because I had to get the backup of 26/02 to get access to the missing tables.
Thank's again for the support.
Best Regards,
Hans
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply