Currently, our SQL install has many files listed in the database properties - files section. Here is a screenshot:


Is there any way i can consolidate these files into a standard MDF and LDF file (on different drives perhaps)

(i should add - it wasn't me that set this instance up!)

Also, why is one of the files extension "ndf" - is that just a typo?


The person who created the database specified an additional filegroup to hold the application data (.NDF) and to keep it separate from the Primary filegroup (.MDF). This is actually good practice and can help restoration in the event of corruption.

There are also several transaction log files. I expect the additional ones have been set up in the past to overcome space issues on your c: partition. I'd consolidate these into one file and move it to a different partition to your data files - one with an appropriate amount of space. You can do this by shrinking the transaction log to its smallest size, deleting the additional log files and then move the one remaining log file by detaching, copying and reattaching.

To ensure your log file doesn't grow too large make sure you have the correct recovery model set. If you're using the fully logged recovery mode remember to schedule regular transaction log backups.

I'd also recommend setting an appropriate initial size and growth rate for the log file (rather than the 1MB, 10% defaults). E.G. 1GB initial size, growing by 512MB.

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.