I have to following problem: since my database runs out of memory in a few days, I am looking for a way how to make the database use more than one file. I would like to store upcoming data in another file, which is located on a different hard drive.
I am using SQL Server 2008. Under "database properties" I already found out I can create more logical files. I created one in the filegroup PRIMARY. Anyway, SQL Server doesn't use the new file. What can be the reason for this?
Do I have to limit the file size of my "primary" file to make it use the new file? I don't want to wait until my disc runs out of memory, hopefully somebody has some way to solve this!
Thank you very much in advance!
tommy
Best Answer
First and foremost lets make sure you did due diligence and have determined that is the data file that had grown, not the log. IF the log is growing, read How to shrink the SQL Server log and Factors That Can Delay Log Truncation.
Do you have more that one filegroup, ie aside from PRIMARY, is there another one? SQL Server tables are stored in individual file groups (strictly speaking is partitions of indexes (including index 0 and index 1, better known as the 'table') that are store on individual file groups. If you have more than one filegroup and the table that grows is not in PRIMARY then SQL cannot use the new file for the 'big' table. You need to add the file to the filegroup that has the size problem.
But, before adding a file, I would recommend you investigate how is the space used. Which table has grown and why? Are you wasting space?
sp_spaceusedwill be your friend to investigate this. PErhaps you'll discover that you can reclaim the used space and save you from the troubles of an ever increasing file.