Solved

Auto growth on Index file

Posted on 2013-01-14
1
461 Views
Last Modified: 2013-01-15
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
Comment
Question by:navajo26354
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38776628
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
get_systemdrive info from tsql? 1 22
Add a step to a system backup job 6 32
Need return values from a stored procedure 8 40
How come this XML node is not read? 3 27
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question