October 3, 2012 at 10:31 am
TJ-356724 (10/3/2012)
SELECT Object_Name(544525519) returns a NULL record. I have about 3 of them. How can I get rid of them?
On which system, 2000? or post-upgrade on 2008?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 11:27 am
SQL 2000
October 3, 2012 at 11:35 am
OK, you have orphaned columns for a table that no longer exists. Here is how you can correct the problem on the 2000 instance:
http://msdn.microsoft.com/en-us/library/aa258741(v=SQL.80).aspx
** Take a FULL backup first and make sure the backup is valid (restore it somewhere else) before doing this.
If after doing this DBCC CHECKCATALOG does not run clean post the new errors.
If it runs clean you're good to move forward with your migration.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 11:42 am
It's worth noting this is much more difficult to fix on SQL 2008, so you want to fix it and get a clean CheckCatalog before upgrading.
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
October 3, 2012 at 11:48 am
Running DBCC CHECKCATALOG ('TWORKS') WITH NO_INFOMSGS produces some errors as follows:
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 544525519 (object '544525519') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Then running the suggested script :
USE master
GO
SELECT * FROM syscolumns
WHERE syscolumns.id NOT IN
(SELECT sysobjects.id FROM sysobjects).
returns no records
October 3, 2012 at 11:50 am
TJ-356724 (10/3/2012)
Running DBCC CHECKCATALOG ('TWORKS') WITH NO_INFOMSGS produces some errors as follows:Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 544525519 (object '544525519') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Then running the suggested script :
USE master
GO
SELECT * FROM syscolumns
WHERE syscolumns.id NOT IN
(SELECT sysobjects.id FROM sysobjects).
returns no records
Change
USE master
to
USE YourDatabaseWithTheProblem
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 12:06 pm
that works..will advise results
Thanks
October 3, 2012 at 12:22 pm
There are 3 distinct id in the error list. In examining them on older database it has been determined that they are pre 2005 tables.
October 3, 2012 at 1:25 pm
All errors are gone.
Thanks for everyone's help.
October 3, 2012 at 1:42 pm
Good to hear. Take a backup, restore that to SQL 2008 and run CheckDB there, make sure that comes out clean as well. It's just a test-restore, not your planned upgrade.
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
October 3, 2012 at 2:16 pm
Thanks Gail. I planned to do just that in the a.m.
P.S.
I love your articles.
October 4, 2012 at 6:27 am
Restored to 2008 this a.m.
No errors using DBCC CHECKCATALOG
DBCC CHECKDB reurned the following:
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Lots and lots of the following:
DBCC results for 'txns9807'.
There are 1 rows in 1 pages for object "txns9807".
DBCC results for 'MRF_After'.
There are 78 rows in 1 pages for object "MRF_After".
DBCC results for 'MRF_vsousa_PJNew'.
There are 1090 rows in 40 pages for object "MRF_vsousa_PJNew".
DBCC results for 'WorktEST'.
There are 0 rows in 0 pages for object "WorktEST".
DBCC results for 'EmployeeServicesTSS'.
There are 0 rows in 1 pages for object "EmployeeServicesTSS".
DBCC results for 'ContactDocSearch'.
There are 0 rows in 0 pages for object "ContactDocSearch".
DBCC results for 'MRF_MA_NE_Q4_2007'.
There are 269 rows in 8 pages for object "MRF_MA_NE_Q4_2007".
DBCC results for 'AsgSearch'.
There are 9 rows in 1 pages for object "AsgSearch".
DBCC results for 'NYStaffingTemp'.
There are 159 rows in 11 pages for object "NYStaffingTemp".
DBCC results for 'twwc_HtmlText'.
There are 11 rows in 1 pages for object "twwc_HtmlText".
DBCC results for 'SupportCasePriority'.
There are 4 rows in 1 pages for object "SupportCasePriority".
DBCC results for 'DTIndexFileName'.
There are 7 rows in 1 pages for object "DTIndexFileName".
DBCC results for 'MRF_ASD_DrugDed_Archive'.
There are 5059 rows in 119 pages for object "MRF_ASD_DrugDed_Archive".
Finally:
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Tworks).
Not sure about the consistency error. Thoughts?
October 4, 2012 at 6:29 am
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Run it and post the results. Doesn't sound serious.
Run the same on SQL 2000, see if that returns any consistency errors.
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
October 4, 2012 at 6:57 am
2000 RESULTS
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'AssignmentRoot' (ID 1286229770). Missing or invalid key in index 'idx_AssignmentRoot_RepName' (ID 70) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:1086898:0) identified by (RID = (1:1086898:0) ItemID = 738417) has index values (RepName = 'House_PV' and ItemID = 738417).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'AssignmentRoot' (object ID 1286229770).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (Tworks ).
2008 RESULTS
Msg 8951, Level 16, State 1, Line 1
Table error: table 'AssignmentRoot' (ID 1286229770). Data row does not have a matching index row in the index 'idx_AssignmentRoot_RepName' (ID 70). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:1086898:0) identified by (ItemID = 738417) with index values 'RepName = 'House_PV' and ItemID = 738417'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'AssignmentRoot' (object ID 1286229770).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Tworks).
October 4, 2012 at 7:09 am
If you have an opportunity could you please give an opinion on this one also that happen a couple of days ago. The mapped drives are SAN based.
http://qa.sqlservercentral.com/Forums/Topic1367638-1550-1.aspx
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply