Re: Orphan Indexes?
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/05/04
- Next message: oj: "Re: Appending text to existing value in table column"
- Previous message: Hari Prasad: "Re: Database permissions"
- In reply to: Andrew: "Re: Orphan Indexes?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 5 Aug 2004 09:37:14 +0200
Consider yourself lucky that you didn't work out how to delete the rows from sysindexes. By doing that, you
would still have the index pages allocated, i.e., a corrupt database.
Can you back trace the event that lead to this? I.e., can you create a repro-script that we can execute in a
test database? "If it bleeds, we can fix it". I.e., if we can repro it, we can possibly find a fix, or if it
is a bug in SQL Server, MS can probably rather quickly find a fix if you open a case...
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Andrew" <AndrewR2k1@hotmail.com> wrote in message news:%23TssUeneEHA.2916@TK2MSFTNGP12.phx.gbl... > I have tried performing the DROP as both part of and outside of the stored > procedure. I tried the DROP from Query Analyzer and from the helper > program. I have tried changing the owner of the table and then performing > the DROP in the manners described iin both the previous two sentences. > > End result: no change. > > I cannot get rid of these indexes. I have even tried openign the > "sysindexes" table through SQL Server Enterprise Manager, and attempting to > manually delete the records containing these indexes. I am denied access. > > I am out of ideas. Nothing I try works, the indexes are like white on rice. > Help?? > > -- Andrew > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:u04Z9BleEHA.2028@tk2msftngp13.phx.gbl... > > Ahh, perhaps you are doing the DROP inside the proc, so the parser don't > know of the existence yet (as they > > haven't been created at parse time... or something like that. What if you > use dynamic SQL to execute the DROP > > INDEX statement? > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > "Andrew" <AndrewR2k1@hotmail.com> wrote in message > news:uVq5dhkeEHA.2044@TK2MSFTNGP10.phx.gbl... > > > Tried this already, still have the same problem. > > > > > > drop index dbo.t.x > > > drop index (username).t.x > > > > > > Makes no difference > > > > > > -- Andrew > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > > message news:%23y5RhYkeEHA.4068@TK2MSFTNGP11.phx.gbl... > > > > Perhaps an owner issue for the table? You can specify the table owner > in > > > DROP INDEX, as below shows: > > > > > > > > create table t(c1 int) > > > > create index x on t(c1) > > > > drop index dbo.t.x > > > > > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > http://www.karaszi.com/sqlserver/default.asp > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > "Andrew" <AndrewR2k1@hotmail.com> wrote in message > > > news:%23xuiWBkeEHA.1732@TK2MSFTNGP09.phx.gbl... > > > > > Hey all, > > > > > > > > > > I have run into a problem and I am completely lost.. I wrote a > couple > > > > > stored procedures to be used by a little helper program to delete > and > > > remake > > > > > a table, upload some data to the DB from a text file the program > read > > > in, > > > > > and finally, apply a couple indexes to the newly made table > > > (TempPri2004). > > > > > > > > > > I ran into a snag in that when the program ran the SP's, the owner > of > > > the > > > > > table is not the same as when I created the SP's in Query Analyzer. > So, > > > I > > > > > added to the program a step that would run the SP > "sp_changeobjectowner" > > > to > > > > > change the owner so my SP's would run. But now I have a major > problem. > > > > > > > > > > I can no longer get rid of the indexes my SP created in the final > step > > > of > > > > > the process. If I run the following query, I get a record back with > the > > > > > Index name (it was found, in other words): > > > > > > > > > > "SELECT name FROM sysindexes WHERE name = 'NC_RaceLabel'" > > > > > Records returned: NC_RaceLabel > > > > > > > > > > But if I try the following, it bombs with the error given: > > > > > > > > > > "DROP INDEX TempPri2004.NC_RaceLabel" > > > > > Error: Cannot drop the index 'TempPri2004.NC_RaceLabel', because it > does > > > not > > > > > exist in the system catalog. > > > > > > > > > > If I open SQL Server Enterprise Manager, expand the database for > this > > > table, > > > > > right click the table, and select Manage Indexes, there is no items > > > listed > > > > > in the box. > > > > > > > > > > I am at a loss here. The indexes are listed in the "sysindexes" > table > > > if I > > > > > query the table, but I am unable to drop them. Help?? > > > > > > > > > > -- Andrew > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: oj: "Re: Appending text to existing value in table column"
- Previous message: Hari Prasad: "Re: Database permissions"
- In reply to: Andrew: "Re: Orphan Indexes?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
