1

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

  • 1
    Are the indexes created with many included columns? Could you update your question with a couple of samples of the index definitions that are causing your problem? – RLF Feb 2 '15 at 14:25
3

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.

  • + Thomas it would be good if you add a line in your answer saying that please use Ola Hallengren soluion for index maintenance. I guess OP is using MP to rebuild indexes so this is what causing major issue – Shanky Feb 2 '15 at 16:45
  • @Shanky - Although I do use Ola's solution, it does not seem clear that the OP using Ola's solution would resolve his problems. – RLF Feb 2 '15 at 16:58
  • If you note OP said when I rebuild index whole RAM is eaten up and server becomes almost unresponsive that made me guess he must be doing it through MP. – Shanky Feb 2 '15 at 17:01

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.