scripts to move indexes to diffrnt file groups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Notification Services » Administration » scripts to move indexes to diffrnt file...

scripts to move indexes to diffrnt file groups

Rate Topic

Display Mode

Topic Options

Author

Message

janardhan4891

Posted Friday, December 23, 2011 4:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:20 AM
Points: 2, Visits: 47

need some assistance regarding scripts...The requirement as follows
-------------------------
Move all non-clustered indexes to INDEX file group
Move all user (U) type tables and clustered indexes to DATA file group

For the above,we have 4 schemas and 150+ tables in the database,can we move all indexex/tables at a time using scripts

Post #1226169

GilaMonster

Posted Friday, December 23, 2011 5:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Have you checked the script library here or a google search? There should be something to get you started. It's not trivial though.

One question though. Why are you doing this? What's the reason behind moving nonclustered indexes to another filegroup.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1226172

janardhan4891

Posted Friday, December 23, 2011 6:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:20 AM
Points: 2, Visits: 47

Checked in script library,but not found the script

Post #1226201

Chuck Hottle

Posted Friday, December 23, 2011 11:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 197, Visits: 1,068

You can either drop the existing index and create the new one on the new file group or use WITH (DROP_EXISTING = ON), instead of explicity dropping it first.

Post #1226347

SQLRNNR

Posted Saturday, December 24, 2011 12:30 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

janardhan4891 (12/23/2011)

Checked in script library,but not found the script

Have another look. I remember seeing a couple of these scripts in there.

Just curious, and like Gail asked, why are you looking to make these moves?

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1226418

salum

Posted Saturday, December 24, 2011 3:00 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, September 23, 2012 8:09 AM
Points: 70, Visits: 157

Here are the scripts. Smile

http://www.sqlservercentral.com/scripts/31541/
http://www.sqlservercentral.com/scripts/63620/
http://www.sqlservercentral.com/scripts/63620/

java

Post #1226446

« Prev Topic | Next Topic »

Permissions