Sql-server – Index file growing endlessly – how to fix it

index-tuningmaintenancesql serversql-server-2008-r2

I need to do some maintenance on a database on our client's site. Both log and index files were huge (Don't know how they took their backups to end this way) anyway I fixed the log file (.ldf) issue, log file is now merely a few megabytes (database is in simple recovery mode)
The problem resides on the index file (.ndf) side. For comparison purposes, the database itself (data side, .mdf) is 265Mb. The index file (.ndf) is 57.3Gb!!

Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high. On our test server, the operation wasn't ended after a full 8 hours activity. I can't do this on their server since this is a 911 emergency center.

I just wish to flush the whole index file data and then rebuild it properly. Is there any way to do this without having to render the server in an almost unusable state during the process and having to put the database offline?

Regards,

Nicholas

Best Answer

You're confusing a few terms, I think. The ndf file is not an "index file", it is just another database data file to house the data.

It sounds like you have two database data files (the MDF and NDF). With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the "other" filegroup using the NDF file).

You need to do a little discovery here.

What are the filegroups in your database?

select 
    name,
    type_desc,
    is_default
from sys.filegroups;

Which database files belong to which filegroups?

select
    file_name = df.name,
    df.physical_name,
    filegroup_name = f.name
from sys.database_files df
inner join sys.filegroups f
on df.data_space_id = f.data_space_id;

And which data space do all of the allocation units live in, and how much are they?

select
    data_space_name = ds.name,
    total_pages = sum(au.total_pages),
    total_pages_mb = 
        convert(decimal(11, 2), sum(au.total_pages) * 8.0 / 1024)
from sys.allocation_units au
inner join sys.data_spaces ds
on au.data_space_id = ds.data_space_id
group by ds.name;

My guess is that you have two filegroups, PRIMARY and another one. The other filegroup probably has the NDF file that you are looking at linked up with it. And maybe even the "other" filegroup is the default filegroup. This is actually a prudent approach, so that you keep user objects off of the PRIMARY filegroup.

Why do you think this is a problem? Maybe you just have 57.3 GB worth of data in this database now. Don't be thrown off by the differing sizes of the MDF and NDF (unless they are in the same filegroup, in which case update your question with the specifics from my diagnostic queries above).

EDIT

Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high

You should only be selectively rebuilding/reorganizing your indexes based off of their fragmentation levels. As for the memory consumption, there are ways to minimize that impact such as limiting by altering the max server memory server configuration so that SQL Server doesn't over consume memory and then battle with the OS.

Related Question