1

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 gb of space on a rather expensive storage. Indexes of the db is on its dedicated filegroup and I created this one the same. Even though I stopped the execution, the 10 gb of space was not released to the OS. The index file has not much free space either. My question is; where has my 10 gb gone? Thank you,

2

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size.

SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of data (rows, indexes) to the database before it will need to grow the file size again since that space is unused.

You can right click and select properties of the database in question in Management Studio to see how much space is free of its total size:

free space

If you must shrink, you can read up on it at the MSDN article on how to Shrink a File.

  • Thanks mate. I have 4 data files and 1 log file. One data file is dedicated to the indexes (by filegroup). Whenever I create an index, it is created on index's filegroup (the only file on that filegroup). My question would be: 1) The index file is, for example 50 gb and its free space is 500 mb. So, is it possible that there might be some remnants of the index creation? 2) Is there a way to track file grow historically so that I can watch previous hours? I wonder if it really grew. 1) What does "space available" in the screenshot denotes? Which file's space does it refer? Thank you. – Stackoverflowuser Nov 6 '14 at 15:05
  • 1
    1) No, either the index exists or it does not. 2) You could write a job to query the file size and store it in a table. 3) Space available refers to the entire database, all data files included, that is not currently used. You can see individual file usage by right clicking the database, Tasks -> Shrink -> Files and then selecting files from the drop downs. Again do not actually shrink files unless you are sure you are need to. – LowlyDBA Nov 6 '14 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.