0

I have created a non-clustered index on table columns and it contains 5GB of index data but I am not able to see this +5GB data size change on my disk.

Any suggestions?

Thanks

canpoint
  • 817
  • 2
  • 9
  • 19
  • Why would you see it on the disk? The index is in the database. – Gordon Linoff Jan 05 '15 at 14:17
  • If your data file(s) had 5+ GB of free space, they would not need to grow and the file size(s) would remain the same. – Dave.Gugg Jan 05 '15 at 14:23
  • @Dave.Gugg, lets say my mdf and ldf are on F disk, now I have 15gb of free space on disk. After I had created index of 5GB, then size will not change? – canpoint Jan 05 '15 at 14:30
  • 1
    No not necessarily. SQL grabs more disk space than it needs, and doesn't grab more until it needs it, and then again it grabs more than it needs. Otherwise it would be growing every time it needed the slightest bit more space. It prefers to grow less frequently, in big chunks. – Tab Alleman Jan 05 '15 at 14:48
  • @TabAlleman thanks I got it! also check the answer below – canpoint Jan 08 '15 at 12:50

1 Answers1

0

You can see how much space is being used within a SQL mdf or ndf file with this query:

SELECT  df.name ,
        df.size / 128 AS FileSize ,
        df.size / 128 - fsu.unallocated_extent_page_count / 128 AS UsedSpace
FROM    DatabaseName.sys.dm_db_file_space_usage fsu
        INNER JOIN DatabaseName.sys.database_files df ON fsu.file_id = df.file_id

If the file had enough free space before, it would not have to grow when you created the index.

Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43