Growth of index file
-
Hello,
I have a Std Ed SQL2000 database which is part of a vendor application. The data file has been slowly growing and is now at 12 GB. However, my concern is for the index file (.ndf). It is 21 GB and growing faster than the data file.
The application is an email archiving program (required for compliance). So I need to be very careful not to upset the system. I run maintenance jobs on the week-end which perform the full backups, log backups, integrity checks and optimization. These processes are successful and shrinking the log file to about a quarter gig, but are ot controlling the growth of the index file.
I've tried some manual shrinking using dbccShrinkfile. That process seems to shrink either the data or index file, but simultaneously increases the other one.
I'm assuming the large index file arises from the text search capability of the program, but I would like to control the growth before it becomes a problem.
Can someone point me towards resource to learn how to control growth of indexes?
Thanks,
Elliott
-
If you have several large indexes on a table it is entirely possible the index file will grow faster than the data files. Take a look at the structure of the indexes and see if all of the index fields are requried. I've seen bad index design eat up lots of space on several occasions.
-
Avoid doing a shrinkfile if you know the file will grow again. Repeated shrinkfiles will result in disk fragmentation which will harm performance. You can only cure disk fragmentation by doing a disk defrag - a index rebuild will not impact this problem.
As Ross says, you can get more space taken by indexes than by the underlying data, especially if the index design is bad.
I suggest you contact your vendor, and ask them to verify that your application is working correctly. If it is, then your organisation will have to budget for the amount of disk space the application uses.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thanks for both replies. I will take your advice about avoiding shrinkfile operations.
My impression of the vendor's index strategy was that the indexes covered every possible column. And my questions to the vendor support staff about file growth have not been addressed. The underlying server disk space is small by today's standards, so I have been forced to make the T-Log and full backups directly onto a remote disk.
Unfortunately, because this application serves a compliance requirement for my company, I think it best not to alter the indexes or any other aspect of their program. I don't want to have to defend my actions to the regulators in case something unexpected crops up.
So thanks again, I wanted to make sure that I'm not over-looking a simple answer.
Elliott
-
Are you sure that the NDF is just indexes? NDF files are just additional data files, which can store indexes or data.
Since this is for compliance, I'd think about getting more disk space. The need will grow and you'll only be archiving more data, so until you figure out the rolloff point and how large it will actaully get, you might want to drop some nice big 70GB or larger drives in there.
-
Good point. The vendor named the file "xxx_index.ndf", so I'm making an assumption here. But I think it's correct. Is there any way to check it?
I agree on the disk space solution. Apparently the existing server was judged totally adequate way back when. And I think the application was "upgraded" to facilitate full text searching of archived emails for legal purposes. So IT knows that more disk space would be beneficial, but at the moment it isn't the highest capital budget priority (and no one is anxious to make any changes to this compliance system).
So I'm looking to see what DBA tools might prolong the life of the existing system. Thanks to everyone for the responses.
Elliott
-
Try dbcc dbreindex
-
Be careful with dbcc dbreindex. If you turn it loose against all tables/indexes you could wind up with a transaction log the size of Texas! Check BOL and various other online resources for appropriate methods to perform routine index maintenance. I believe Kalen Delaney has some good scripts you should be able to find.
What is the fragmentation of the indexes? Are they mostly full pages or half empty? If the latter, they could well be taking up almost twice as much space as they optimally require.
Best may be to rebuild/reorg your indexes one at a time, with a log backup inbetween (or at least check how full the log is with dbcc sqlperf(logspace))
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply