Red Gate forums :: View topic - Exception on multiple indexes with identical name
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Dependency Tracker 2
SQL Dependency Tracker 2 forum

Exception on multiple indexes with identical name

Search in SQL Dependency Tracker 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
florian kistner



Joined: 17 Jul 2014
Posts: 2

PostPosted: Thu Jul 17, 2014 2:12 pm    Post subject: Exception on multiple indexes with identical name Reply with quote

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:

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:

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6678

PostPosted: Fri Jul 18, 2014 4:46 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
florian kistner



Joined: 17 Jul 2014
Posts: 2

PostPosted: Fri Jul 18, 2014 5:07 pm    Post subject: Reply with quote

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:
Quote:

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/ms188783(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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6678

PostPosted: Mon Jul 21, 2014 9:27 am    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group