April 7, 2009 at 8:42 am
Hello,
Sorry to bring this topic back up but I am getting this same error and I cannot figure out what was done to fix it.
I checked master, model, msdb and they are all set to grow unlimited on a disk drive that has approximately 100GB of free space.
I'm trying to add a datafile to my PRIMARY filegroup. I have 20GB left of free space there but need a lot more. I've got an additional drive of over 800GB that has nothing on it. I'm trying to put a 50GB data file there and keep getting the above error and I am at a loss. I can add space to existing datafiles but this does not seem to help.
Any suggestions? Any more info needed? I've been using Oracle for the past 1.5 years and I'm just getting back into SQL Server so apologies if I've made a silly/simple mistake.
Thanks.
EDIT: I figured this out. My issue is that i have 15 or so datafiles in PRIMARY and when I was adding more space I was not adding it to the FIRST datafile in the PRIMARY filegroup. I set the autogrowth parameter on the first datafile to grow a bit more and I was able to add data files.
So now I have a new question - is it bad design to build a database primarily on the PRIMARY file group (pardon the pun)? I did not consider this when building this database, I assumed most "system" info was in master. Thanks again!
April 7, 2009 at 9:02 am
I had this issue. I increased the max size of the first file in the primary filegroup (thus allowing it to grow). I was then able to add new datafiles.
If you post the results of this query (run in the db you want to add a new data file). I can see the state of the current data files
select f.fileid
,f.name
,f.size
,f.maxsize
,f.growth
,f.status
,fg.groupname
fromsys.sysfiles f
left join sys.sysfilegroups fg
onf.groupid = fg.groupid
order by f.fileid
April 23, 2009 at 4:38 am
Jason T (4/7/2009)
So now I have a new question - is it bad design to build a database primarily on the PRIMARY file group?
Depends on the database. We have several databases built only on the Primary file group and only a few with multiple file groups. We've never had a problem. Of course, we're not running VLDBs either.
The thing about different file groups is it gives you some flexibility you don't have with only one filegroup. The problem with multiple file groups is maintenance. So really, it depends on your needs. If you don't need them, don't use them.
April 23, 2009 at 4:47 am
Can't you create / extend file on new drive if you have?
April 23, 2009 at 5:06 am
murthykalyani (4/23/2009)
Can't you create / extend file on new drive if you have?
This might have been suggested by other friends.So, Please ignore this post, didn't read other posts and acted immediately.
April 23, 2009 at 5:10 am
murthykalyani (4/23/2009)
Can't you create / extend file on new drive if you have?
You don't need multiple filegroups to put files on different drives. Just multiple files themselves. They can all be in the same filegroup. @=)
April 23, 2009 at 6:52 am
You are correct that is what I meant to say.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply