Getting error 'PRIMARY' filegroup is full

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Getting error 'PRIMARY' filegroup is full

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-19 : 12:29:10

I am getting the below error when loading the data into the tables using DTS packages:
Error: Could not allocate space for object table1 in database DB1 because the 'PRIMARY' filegroup is full.
Not sure what's causing it. I greatly appreciate all the help!
Here are the things I checked:
1.There is enough drive space: has 87 GB free space
2.Database: Data gets loaded every night into the tables using dts packages, so the data changes only with the DTS packages.
data file size: 37.3GB
log file: 63MB
*Options:
RecoveryModel: simple(only db backup is taken daily, tran log is not backedup)
Autoupdate statistics is checked
Autoshrink is checked
Auto create statistics is checked
*Data file:
Space allocated(MB): 36479
Automatically grow file is checked
File growth: In megabytes: 10
Unrestricted file growth is checked
*Transaction Log:
Space allocated(MB): 62
Automatically grow file is checked
File growth: By percent: 10
Unrestricted file growth is checked

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:49:22

Uncheck AUTOSHRINK!

Which edition and version number of SQL Server 2000 are you using?

E 12°55'05.25"
N 56°04'39.16"

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-19 : 13:16:32

quote:
Originally posted by Peso

Uncheck AUTOSHRINK!

Which edition and version number of SQL Server 2000 are you using?

E 12°55'05.25"
N 56°04'39.16"

Thanks for the quick response,
I just unchecked autoshrink on that db, Is autoshrink not preferred?
I have most of the databases with autoshrink on.
It's Microsoft SQL Server 2000 - 8.00.760
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:18:23

Is the primary filegroup fragmented? So that there is no contigous space left to increase file?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 13:19:27

Autoshrink should not be turned on for any production databases. You don't want it to kick-off at random times of the day and suffer a performance problem.

To fix your problem, manually expand the MDF file a bit to accomodate your processes. The automatic expansion must not be happening fast enough for your process.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-19 : 14:16:47

If we turn off autoshrink, then do we have to shrink the data and log file's of the production databases as a weekly scheduled job?
Manually expanding the MDF file: Is it applying dbcc shrink file on data and log files?
Thanks a bunch!!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 15:16:10

No, don't shrink them. That's whole point of disabling auto shrink.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 15:17:28

>> manually expanding the MDF file: Is this increasing the space allocated that's present under data files tab

Yes.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 15:43:46

>> Manually expanding the MDF file: Is it applying dbcc shrink file on data and log files?

It's nothing to do with dbcc shrinkfile at all.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-19 : 16:26:50

quote:
Originally posted by rmiao

>> manually expanding the MDF file: Is this increasing the space allocated that's present under data files tab

Yes.

Do I need to bring down the database while increasing the space allocated for data files? Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-19 : 16:32:54

No. It's the same process as when SQL Server automatically does it when you have auto grow turned on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-19 : 17:06:52

quote:
Originally posted by tkizer

No. It's the same process as when SQL Server automatically does it when you have auto grow turned on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thanks Tara! I increased the space allocated by about 321MB(orig it was 36479MB, now it is 36800MB), I shall see tonight as the DTS packages are scheduled to run at 11PM.
Currently for tran log: space allocated is 62MB, is this okay?
I appreciate all the help.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 20:49:41

Let log file auto grow.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-20 : 10:34:59

The DTS package that loads the sql tables just loaded 1 table last night and the other tables are'nt yet loaded and the sql job shows that it's still executing. So I just cancelled the job as it usally is done by 3.30AM.
I checked the application log in the event viewer and I see the below informational type message, not sure how I can solve this issue, any help is appreciated. Thx.
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17177
Date: 12/20/2007
Time: 12:00:30 AM
User: N/A
Computer: server1
Description:
This instance of SQL Server has been using a process id of 896 since 12/16/2007 6:05:16 PM (local) 12/16/2007 11:05:16 PM (UTC).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-20 : 12:02:41

There is nothing to solve with that message. It has nothing to do with your DTS package or job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-20 : 16:06:00

Why stop the job? Did db file get full again?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources