Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    61

    Unanswered: Index file grown bigger than data file day by day

    Hi all,

    I have a serious problem:

    I created 1 database with 2 file group : 1 primary and 1 index.
    - Primary file group includes 1 data file (*.mdf): store all tables
    - Index file group includes 1 index file (*.ndf): store all indexes

    Most of indexes are non-cluster indexes

    After a short time using, data file is 2GB but index file is 12 GB.
    I do not know what problem happened in my database.
    I have some questions:

    1/ How so I reduce size of index file ?
    2/ How to know what is stored in index file?
    3/ How to trace all impact to index file?
    4/ How to limit size growing of index file?

    Thanks in advanced

    Thi Nguyen

  2. #2
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    smaller indicies

    If your tables aren't updated often I believe the size of index is reduce with a higher fillfactor (like 99). Also check if your indexes are fragmented (see DBCC SHOWCONTIG). You can compact indexes with DBCC INDEXDEFRAG

  3. #3
    Join Date
    Jul 2004
    Posts
    61
    thanks for your help

    Thi Nguyen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •