Attaching db strips index properties

  • Okay, I must be doing something very wrong here. Here's what's happening:

    1. Database residing on Server A has various pad_index and fill_factor values and on indexes throughout the db.

    2. Stop Server A.

    3. Copy files to machine B where Server B is installed.

    4. Attach Database to Server B.

    5. All indexes in Database on Server B now have fill_factor set to zero, pad_index=off.

    I've reproduced this five times now. What am I doing wrong? I can't seem to get this database from one server to another without losing all the index properties.

    EDIT: On further review, this appears to be a SSMS issue. sys.indexes shows the correct index properties, while bringing up the properties dialog on the index does not. Bizarre. I'm still curious about it, but no longer terrified. 😉

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • That's an odd one. What version of SSMS are you running and what version of SQL Server is the server itself? You may need to update both.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The only time I had a similar issue was an id10t issue in the script (db context wrong).

    Since copying the files doesn't edit them, that just can't happen.

    Maybe you were looking at the default FF instead?

  • Ninja's_RGR'us (10/19/2011)


    The only time I had a similar issue was an id10t issue in the script (db context wrong).

    Since copying the files doesn't edit them, that just can't happen.

    Maybe you were looking at the default FF instead?

    There's only one db on the target server, the one I'm attaching through SSMS. SSMS index properties dialog (for the index, not the server default, which isn't zero anyway) shows zero fill factor, sys.indexes shows the correct values. There isn't a huge margin for an id10t error here. I'm confident the indexes have not been altered, now that I've taken the step of querying sys.indexes.

    After further testing, I believe the issue is limited to SSMS 2005. When I attach the same db to the same server using SSMS 2008 R2, the index properties dialog shows the correct values. Problem solved, just be sure not to use SSMS 2005.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Thank for the feedback.

    P.S. I've confirmed your findings in my sql 2005 SP3-ish environement.

    Shows FF = 0 in the GUI but scripts out to the correct 85 FF.

    P.P.S. I never meant to say you were the one executing the 10 t error :-).

  • Ninja's_RGR'us (10/19/2011)


    P.P.S. I never meant to say you were the one executing the 10 t error :-).

    Oh I know. I made a conscious decision not to take it that way. Enough tension in the world, dontcha think? 😉

    thanks

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • ronmoses (10/19/2011)


    Ninja's_RGR'us (10/19/2011)


    P.P.S. I never meant to say you were the one executing the 10 t error :-).

    Oh I know. I made a conscious decision not to take it that way. Enough tension in the world, dontcha think? 😉

    thanks

    ron

    After 18K posts I've had enough cases where if I think it's borderline, I better re-explain :hehe:.

    Glad you're on the "good" side of things!

  • Ninja's_RGR'us (10/19/2011)


    Glad you're on the "good" side of things!

    How could I look at that cute little face and assume there was potential for even a shred of evil behind it?

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • ronmoses (10/19/2011)


    Ninja's_RGR'us (10/19/2011)


    Glad you're on the "good" side of things!

    How could I look at that cute little face and assume there was potential for even a shred of evil behind it?

    ron

    Maybe you should re-re-re-rewatch star wars :-D.

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

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