Solved

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

Posted on 2013-11-12
2
2,202 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now