I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing about 2 or 3 GB ( this is also strange but there about 6 millons lines of CDR logs imported and processed ) but last time we tried. Even if the free space is about 11 GB. Reports can not be generated. Disk spaces being empty unexpectedly.
So make some search and see some advices to check the indexes. According to the indexes fragmentation there may be some disk spaces issues.
Firstly; does index fragmentation causes disk spaces unefficient use ?
Second; what would happen if I make index reorganize or rebuild ? Of course there is a risk of DB crash always but would it be harmfull for the DB ?
Best Answer
There are two different types of fragmentation to worry about: physical and logical. Physical fragmentation means the file that stores your indexes is fragmented at a file system level. Logical fragmentation means that page splits have occurred while inserted or updating data in an index. Incidentally, shrinking your database files will eventually cause physical fragmentation if (when) your database files are forced to grow to accommodate more data and the space needed for the grow is not contiguous with the existing data file. Also, reorganizing and rebuilding your indexes, unless sorting is performed in tempdb, will, more than likely, cause grows on the file containing your indexes. This could further compound the physical fragmentation issue. This is why shrinking your datafiles is almost never a good idea; they grew for a reason, they will simply grow again, and when they do they will cause physical fragmentation.
Unfortunately, I cannot speak to your first question. I assume that logical index fragmentation will not cause problems at an IO level. However, physical fragmentation will manifest itself as an increased number of random reads, due to the file system fragmentation.
As for your second question, index maintenance should not cause any issues. As a matter of fact, you should already be performing index maintenance as a part of your routine maintenance. An important thing to note is that index rebuilds implicitly update your index statistics with a full scan. However, index reorganizations do not. As a part of my index maintenance, I explicitly perform a full scan statistics update against indexes I reorganize, instead of rebuild.
Finally, I doubt your issue is caused by indexes. It sounds like you are loading a lot of data into either a staging table or a temporary table, prior to running this report. If you are loading into a staging table, perhaps you could move the staging table to its own filegroup, on another storage volume. If you are loading into a temp table, you are probably running into tempdb running out of space. Either way, it appears you either need additional storage or a new filegroup on a different storage medium for your staging table.
Hope this (admittedly too long) answer helps!
Matt