Solved

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

Posted on 2013-11-12
2
2,857 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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