Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-12
2
Medium Priority
?
3,517 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 975 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 525 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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 …
In this article I will describe the Copy Database Wizard 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.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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