Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 2 '13 at 14:33

This question came from our site for professional and enthusiast programmers.

1 Answer 1

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_spaceused will 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.

share|improve this answer
    
thanks for your quick answer. I just created the database, loading a huge amount of data into it. the log file is located on a different drive. I just have one database file, file type Rows Data and one Filegroup, "PRIMARY". i definitely need more space in the near future. I hope there is some option. –  tommynogger Aug 2 '13 at 8:09
    
sorry, I just read your post again. Your suggestion is I add a new Database file to the PRIMARY filegroup? When it will be used by MSSQL? Do I have to limit the size of the original file to make MSSQL use the newly created database file? –  tommynogger Aug 2 '13 at 8:12
    
OK, then the new file will be used when more data is added in the db. Is somewhat bad to have two files of very different sizes in the same filegroup, the best situation is to have equal size files with same growth settings, for a well balanced IO and allocation (use) pattern. Make sure you enable instant file initialization btw. –  Remus Rusanu Aug 2 '13 at 8:12
    
they are going to have a "very" different size for sure. the existing file has 160gb of disk space allocated. do i have to or is it recommended to do the instant file initialization? another question, I'm currently backing up by using the transact sql command BACKUP database_name TO DISK='PATH'; does this kind of backup includes both files? –  tommynogger Aug 2 '13 at 8:23
    
For new questions as a general rule ask a new question, not ask it in a comment. –  Remus Rusanu Aug 2 '13 at 8:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.