Exception on multiple indexes with identical name

Visualizes SQL Server object dependencies.

Moderators: David Atkinson, Anu Deshpande

Exception on multiple indexes with identical name

Postby florian kistner » Thu Jul 17, 2014 2:12 pm

Hello,

I am having trouble with identically named indexes in some of my databases on SQL Server 2008 R2.
It looks like the Dependency Tracker assumes that index names are globally unique, but this doesn't necessarily hold true.
Most prevalent example would be the _AUTO_IDX_1, which depending on the server configuration may be created automatically all over the place.

Code: Select all
Category
Object Loading

Received
17.07.2014 14:35:19

Unable to load objects from database 'REDACTED': AARGH!!!! I've just encountered another object of type Index called '[dbo].[_AUTO_IDX_1]'!

AARGH!!!! I've just encountered another object of type Index called '[dbo].[_AUTO_IDX_1]'!
ReallyBadException

   bei RedGate.DependencyViewer.Engine.DatabaseObjects.AddObject(IDatabaseObjectWrapper obj)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.GrabIndexes(ViewTableSuperClass vt)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.GrabIndexes()
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.WrapObjects(ICancellable cancellable)
   bei RedGate.DependencyViewer.Engine.DefaultDatabase.LoadObjects(LoadBehaviour behaviour)
   bei RedGate.DependencyViewer.Engine.Server.LoadObjects(LoadBehaviour behaviour)



My particular problem is that the Depedency Tracker doesn't resolve any dependencies of the added objects, if it stumples across the auto index.

I do get a lot of "Have no proxy for engine object" as well, but I am not sure these two are related:
Code: Select all
Category
Application

Received
17.07.2014 14:35:19

Have no proxy for engine object #4271 REDACTED.DATABASE.c_fk_MyForeignKeyConstraint


Is this a known issue? Is there a workaround?
Can I tell the Depedency Walker, for example, to ignore all indexes and foreign key constraints? Or to just go on and resolve at least the other dependencies?

-- Florian
florian kistner
 
Posts: 2
Joined: Thu Jul 17, 2014 1:45 pm

Postby Brian Donahue » Fri Jul 18, 2014 4:46 pm

Hi Florian,

Thanks for contacting Red Gate. From my experience, system-generated index names contain a random string of numbers in the name to try to keep them unique. SQL Server should not allow identically-named objects (unless the database is case-sensitive and/or the object name is owned by a different schema).

Can you tell us what version of Dependency Tracker is doing this, and what objects were added that caused the error?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby florian kistner » Fri Jul 18, 2014 5:07 pm

Hey Brian,

to be honest, I am not sure, how these indexes got there, but they don't contain anything random and are all owned by the same schema. We turned off automatic index generation a long time ago, but it seems like some are still around. Could have been some old performance tool that made this poor name choice as well. Anyway - since this is in production I am not going to be able to get rid of those in the short term.

I did some digging and it turns out, Microsoft actually does allow multiple indexes with identical names, as long as they are not created on the same object:
index_name
Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

See http://msdn.microsoft.com/en-us/library ... (v=sql.105).aspx

I am using Dependecy Tracker Version 2.8.1.164, which I downloaded just yesterday as part of the SQL Developer Bundle.

-- Florian
florian kistner
 
Posts: 2
Joined: Thu Jul 17, 2014 1:45 pm

Postby Brian Donahue » Mon Jul 21, 2014 9:27 am

Thanks for pointing this out. I did create a representative sample of a set of tables with an index that is identically-named, and Dependency Tracker was able to add the objects to the view.
Code: Select all
CREATE TABLE [23456] (
id int PRIMARY KEY,
col1 CHAR(10)
)
CREATE TABLE [34567] (
id INT PRIMARY KEY,
col2 CHAR(10)
)
CREATE INDEX [_MY_INDEX] ON [23456] (col1);
CREATE INDEX [_MY_INDEX] ON [34567] (col2)


I'll send you some debugging instructions via email, because we probably need some more information about this schema.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Dependency Tracker 2

Who is online

Users browsing this forum: No registered users and 0 guests