Solved

How to fix "'PRIMARY' filegroup is full issue in SQL SERVER?

Posted on 2013-11-12
2
2,424 Views
Last Modified: 2013-11-25
Often we are getting the below error from NameDB. We are storing ETL Error log info in Errorlog table.  We have same kind of setting in other DB but we are getting alert for only the NameDB. Please let me know what need to set up to avoid this error. DBA team increased the file group space. I guess it is only temp solution. Please advise the permanent solution for this issue. Each tabel has seperate filegrou allocation in DB? Thanks.


Could not allocate space for object 'dbo.ErrorLog' in database 'NameDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
0
Comment
Question by:PKTG
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 325 total points
ID: 39645032
The filegroup is usually per database and by default is named PRIMARY. If you don't create new filegroups there will be only the default one. The database data files, .mdb and .ndf if multiple, are assigned to the default filegroup if not specified otherwise. You need to do few things:

1. check the file settings for that database with:

use YourDatabase
GO
exec sp_helpfile

Open in new window

This will return a result set with all the existing files for that database. Check if the size of any of the files(data or log) has reached the maxsize set for it. If yes you should change it to a bigger size or to unlimited. To change:
USE [master]
GO
-- to change it for a certain value: i.e. for 10GB use 10240000; leave the KB word next to the number
ALTER DATABASE YourDatabase MODIFY FILE ( NAME = N'name', MAXSIZE = <size_here>KB )
GO

-- to make it unlimitted 
USE [master]
GO
ALTER DATABASE YourDatabase MODIFY FILE ( NAME = name', MAXSIZE = UNLIMITED)
GO

Open in new window

for file name in N'name' you will have to use what you see in the "name" column for the corresponding file from the exec sp_helpfile result set.

2.It is possible that the log file has grown to occupy the whole harddrive in wich case you need to setup a transactio log backup. Also you might have to shring the log file with
dbcc shrinkfile(name_log,0)

Open in new window

where name_log is the log file name under the same "name" column I was mentioning above. The log file will not shrink if it was not backed up first.

3. Also is recommendable to make the file growth rate to a number instead to a pectenage, i.e. for 51MB:
USE [master]
GO
ALTER DATABASE YourDatabase  MODIFY FILE ( NAME = N'name', FILEGROWTH = 524288KB )
GO

Open in new window

The idea is to make sure that you have enough space on the drive where your files rezide and also to allow the files to grow. If they will fill the drive at some point you will need to increase the drive size of to add file to the database that will reside on a different drive.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 175 total points
ID: 39645611
Hi,

My suggestion is that either the disk is full, or that the file(s) in the primary file group have had a limit put on their growth.

When you are looking at the file and its growth, I suggest that you think about a more realistic growth value than the likely default of 10%. 10% is okay if the file is a few meg, but when the file is 10 - 100GB that is a lot of growth that whatever is causing the growth is going to be waiting for. And in selecting a different growth, the strong recommendation is to use a fixed size, rather than the percentage.

All the above is easily done from SSMS.

HTH
  David
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question