[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

Auto growth on Index file

I have a index data file which is currently 160 GBs and is fifty percent used.  It is on a 200 GB drive.  I have an index rebuild job that checks for fragmentation and if it's above 80 % it will rebuild.  Currently the file is growing but still continuing to have fifty percent space used.  I do not want this file to grow to fill up the 200 GB drive.  If I stop the auto growth and fix the file size to 160 GB, will it use the free space inside the file or will this create a problem?
0
navajo26354
Asked:
navajo26354
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
Just stoping growth will not automatically cause it to use the space allocated and will eventually lead to an error on unable to grow file.
You may want to check your Fill Factor on indexes.  Check to see if you have any set to something other than the default (0)
SELECT * FROM sys.indexes I WHERE I.fill_factor > 0

Open in new window

If so you may be leaving space unused, see this link at MS about Index fill factor http://msdn.microsoft.com/en-us/library/ms177459.aspx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now