Cannot add a data file Error 1105 because ''PRIMARY'' filegroup is full
-
I am trying to add an additional datafile to the 'PRIMARY' filegroup on a Sqlserver 2000 database.
The error I get when I do this is Error 1105 because 'PRIMARY' filegroup is full.
Extending the exisiting files in the 'PRIMARY' group is not an option for me since I was told if the data files grow beyond 20g it may be an issue, is this true?
Would it be ok to create a Secondary filegroup and add additional files to that?
-
I've never heard anything about data files > 20 gig causing a problem. We have hundreds of databases with data files or files to store indexes that are much greater than 20 gig (several of them over 100 gig).
The only potential problem would be at the hardware level. If disk queuing is not a problem on the drives in which this file resides, I say just grow the current file. Otherwise, add a file, not really a need to create a new filegroup, and place it on a different disk.
Hope this helps...
A.J.
DBA with an attitude -
Now I have a new problem. I created a secondary filegroup and I tried to add two datafiles to it and it gives the following error below after the syntax.
USE master
GO
ALTER DATABASE AeXCRDatabase
ADD FILEGROUP Secondary
GO
ALTER DATABASE AeXCRDatabase
ADD FILE
( NAME = AeXCRReport_Data13,
FILENAME = 'D:\Mssqldata6\MSSQL$ALTGENP1\Data\AeXCRReport_Data13.ndf',
SIZE = 5MB,
MAXSIZE = 20000MB,
FILEGROWTH = 10MB),
( NAME = AeXCRReport_Data17,
FILENAME = 'D:\Mssqldata6\MSSQL$ALTGENP1\Data\AeXCRReport_Data17.ndf',
SIZE = 5MB,
MAXSIZE = 20000MB,
FILEGROWTH = 10MB)
TO FILEGROUP Secondary
Server: Msg 5035, Level 16, State 1, Line 1
Filegroup 'Secondary' already exists in this database.
Server: Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object '(SYSTEM table id: 8)' in database 'AeXCRDatabase' because the 'PRIMARY' filegroup is full.
Extending database by 5.00 MB on disk 'AeXCRReport_Data13'.
Extending database by 5.00 MB on disk 'AeXCRReport_Data17'.
-
- use xp_fixeddrives to check out your current freespace at the "local" drives.
(this proc may be secured)
- there may be file-latency when extending a datafile (from your primary filegroup) (most of the time with these errors)
- how is you file extending (percentages or mb ?) Using % causes your server always to allocate more in an extend, resulting in timeouts in a while. (10% of 20Gb = 2Gb to be allocated )
- just creating a new filegroup will not solve your problem(s) unless you move objects (or create clustering indexes using the new filegroup)
- also check your backup-scenarios making sure that you add the new filegroup or have full backups in place.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
- and off course there should be space at disk-level
- your db already has a filegroup named 'secondary'
choose another name. contact your dba/sysadmin and inform him for your solution !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
So it means if was able to create a secondary filegroup it still would not solve my problem in regards to database objects growing? New objects could be created in the secondary file group? but existing objects could not make use of the secondary filegroup?
What about the error I am getting? How do we solve that problem?
The problem is that we cannot let any of the existing database files grow beyond 20g because of a standard we have. I was told if a database file to large beyond 20g would cause performance issues, is this true? If not then I need documentation that states that.
-
...but existing objects could not make use of the secondary filegroup?...
- If you don't alter existing objects (tables or indexes), they will not use the new filegroup ! You have to point them to the new group ! Check out "create table" and "create index" in books online.
- If your standards state your individual file cannot grow beyond 20Gb, then you might add another file (of 20Gb) to the existing primary filegroup.
- the 20Gb limit is a limit your standards mention. That has nothing to do what so ever with sqlserver. We have a couple of files > 20Gb on one db server without an issue. Offcourse with that kind of volumes, maintenance will need some preparation as well as day to day followup. Also your drives need to have enough capacity to hold > 20Gb files.
- Also keep your backups in mind !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
That is the problem I cannot add any more files to the primary group since the thresehold has been met on that, that Is why I was trying to create a secondary group and adding files to the secondary group also failed, see above the error I got.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply