October 18, 2011 at 1:35 pm
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
October 19, 2011 at 6:13 am
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
October 19, 2011 at 6:21 am
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?
October 19, 2011 at 6:38 am
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
October 19, 2011 at 7:12 am
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 :-).
October 19, 2011 at 8:06 am
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
October 19, 2011 at 8:08 am
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!
October 19, 2011 at 8:11 am
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
October 19, 2011 at 8:15 am
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