Post reply

How to create index on different drive?

  • June 24, 2010 at 4:32 pm

    #141688

    Hello All,

    Can someone tell me the steps i need to perform inorder to keep the entire index on a different drive. Thanks.

  • June 24, 2010 at 7:29 pm

    #1184772

    Hi,

    You would do it for nonclustered indexes only. The steps are:

    1) Create new filegroup:

    ALTER DATABASE DB1 ADD FILEGROUP FG_Indexes

    2) Add new data file (*.ndf) to the database and place it on the new filegroup

    ALTER DATABASE TEST

    ADD FILE

    ( NAME = IND_1,

    FILENAME = N:\MSSQL.1\MSSQL\DATA\IND_1.ndf’,--new drive here

    SIZE = 100MB,

    MAXSIZE = 10GB,

    FILEGROWTH = 100MB)

    TO FILEGROUP FG_Indexes

    GO

    3) Move index to the new filegroup (script index, change option "DROP_EXISTING" to "ON", change filegroup " ON [FG_Indexes]" instead of "ON [PRIMARY]":

    CREATE UNIQUE NONCLUSTERED INDEX [AK_Department_Name]

    ON [HumanResources].[Department]

    (

    [Name] ASC

    )WITH ( DROP_EXISTING = ON) ON [FG_Indexes]

    GO

  • June 25, 2010 at 7:53 am

    #1185003

    got it.Thanks !!!

Viewing 3 posts - 1 through 2 (of 2 total)

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