May 30, 2012 at 1:45 pm
There is a table shown in the SQL 2000 version of Enterprise Manager. It doesn't have a owner listed and we can't delete it ? or do anything else with for that matter.
It is and outdated and unused that that is causing a problem with the RED GATE Compare tool.
Does nayone have any idea how to get rid of it ?
May 30, 2012 at 1:57 pm
What's the error when you try to delete it (try DROP TABLE, not via EntMan) and has someone been messing with direct updates to the system tables?
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
May 30, 2012 at 1:59 pm
here it is:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'BRP001_ACCT', because it does not exist in the system catalog.
I don't think so !
Andy
May 30, 2012 at 2:09 pm
select '[' + name + ']' as objectname, uid from sysobjects where name like '%BRP001_ACCT%'
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
May 31, 2012 at 8:57 am
Gail,
Your query returned no results.
This one "select * from sysobjects WHERE id = 984727327 order by 1"
returned this:
nameidxtypeuidinfostatusbase_schema_verreplinfoparent_objcrdateftcatidschema_verstats_schema_vertypeuserstatsysstatindexdelrefdateversiondeltriginstrigupdtrigseltrigcategorycache
BRP001_ACCT984727327U 11316106127360002002-01-24 16:44:42.253000U 16702002-01-24 16:44:42.2530000000
Andy
May 31, 2012 at 9:48 am
Interesting... Please run your query again, but put the [] around the name like I did.
Also
SELECT * FROM sysusers where uid = 11
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
May 31, 2012 at 10:13 am
Gail..here you go:
I ran this:
select '[' + name + ']'
from sysobjects
WHERE id = 984727327
order by 1
it returned:
[BRP001_ACCT]
SELECT * FROM sysusers where uid = 11
retuned no data
May 31, 2012 at 10:25 am
Someone's been directly updating (or in this case deleting) from the system tables.
If you're feeling brave... and have taken a backup.
EXEC sp_configure 'allow updates', 1
RECONFIGURE
GO
UPDATE sysobjects set uid = 1 WHERE id = 984727327
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
With that done it will regain an owner and you should be able to drop the table normally if you still need to.
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
June 5, 2012 at 8:14 pm
I was finally able to try what you suggested and got this error:
Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '161fbfb13a010000' in index page (1:94567), index ID 2, database 'clients'.
any thoughts on how to handle this and resolve the original issue ?
Thanks,
Andy
June 6, 2012 at 2:59 am
Ok... corrupt database for extra fun. Do you have good backups?
DBCC CHECKCATALOG (<Database Name>)
GO
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Post ALL results.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply