September 6, 2007 at 7:04 am
I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.
But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.
So I will not get too happy about this.
1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?
This all is in SQL Server 2005 Developer and using Management Studio.
Thanks in advance!
September 6, 2007 at 7:32 am
Ge,
the indexes which are greyed out are most likely created for Primary Keys and Unique Constraints. You can't move those, you must drop the constraint and then re-create it on the correct filegroup.
Markus
[font="Verdana"]Markus Bohse[/font]
September 6, 2007 at 7:47 am
Ok, that explains. Thanks, but do you know a way to automate this task? It is not fun to check every table in every database for possible indexes.
September 6, 2007 at 9:08 am
Not sure if someone made a script for something like this, but it's probably worth having a look at the scripts page. http://www.sqlservercentral.com/Scripts/
If you can't find one I would use the generate script task in SSMS to script the tables including all indexes and constraints and when change the filegroup where necessary.
Just make sure you select the options "include drop" and "include Indexes" which by default are set to False.
I know it's some manual work, but shouldn't take too long.
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply