Post reply

Index already exists - EM bug?

  • September 29, 2005 at 1:59 pm

    #165502

    I am trying to create an index in Enterprise Manager, but I am getting this message:

     Index 'IX_CreatedBy_User' already exists.

    The Help says:

     An index already exists for table '<0s>' with the columns '<1s>'.

    This index does _not_ exist in the table to which I am adding it, at least according to EM. In an attempt to view indexes as stored, I ran 'select * from sysindexkeys' in Query Analyzer, but I don't know how to read the 93 rows that it returns. I don't see any of the labels I assigned.

    I have 37 tables and each has a uniqueidentifier-type field named 'GID_CreatedBy_User'.

    I deleted all indexes named 'IX_CreatedBy_User' in all files. Then I was able to add the index to 2 tables, but I received the same error in the 3rd table.

    I experienced the same problem earlier while adding indexes on another field, which is varchar(4) type. I started getting this message after creating that index in about 20 tables.

    Is this an EM bug? Any help will be appreciated.

  • October 2, 2005 at 11:12 pm

    #594490

    Hey!!! Go through this one

    USE <Database_Name>

    SET NOCOUNT ON

    GO

    /*

    **  If the objects already exist (i.e. if this is a rebuild), drop them.

    */

    IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_CreatedBy_User' )

        DROP INDEX  Table.IX_CreatedBy_User

    GO

    /* Again create Index*/

    /*  Create the Index with a FILLFACTOR of 80  */

    CREATE  <NONCLUSTERED/CLUSTERED> INDEX IX_CreatedBy_User ON Table_Name(Column_Name)

        WITH FILLFACTOR = 80

    GO

    SET NOCOUNT OFF

    GO

    Shashank

     

    Regards,

    Papillon

  • October 3, 2005 at 2:51 pm

    #594678

    Mario,

    Make sure you don't have table names which are duplicated.  By this, I am saying if a user logs in with his windows authenticated username and he is dbo, he or another person can create a table with the same name as another username.  For example, you can have two tables with the name of "Products" in your database if they are created by different users.  It would look like this: jsmith.dbo.products and jjohnson.dbo.products

    If this was the case, you could end up with duplicated index names.

    Jules

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • October 3, 2005 at 3:25 pm

    #594689

    "It would look like this: jsmith.dbo.products and jjohnson.dbo.products"
     
    Based on the standard naming convention database.owner.object I believe it is more like: database.jsmith.products and database.jjohnson.products

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 3, 2005 at 3:28 pm

    #594691

    Yes Rudy, you are correct!

    Jules

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • October 3, 2005 at 4:47 pm

    #594701

    This is the dreaded Enterprise Manager refresh problem is it?

     

    --------------------
    Colt 45 - the original point and click interface

  • October 3, 2005 at 4:53 pm

    #594702

    Thank you all for your help. I ended up putting the file name in index names, which made them all uniquely named. This worked. I will have someone with SQL Server experience check the schema before proceeding with it.

  • October 3, 2005 at 4:54 pm

    #594703

    Looks like it was.

  • October 3, 2005 at 4:57 pm

    #594704

    One thing you get to know pretty quickly if you use EM for your development tool. Refresh, Refresh, Refresh and once more Refresh.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply