Friday, March 9, 2012

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 NguyenIf 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|||thanks for your help

Thi Nguyen

No comments:

Post a Comment