Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4078
  • Last Modified:

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

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
PKTG
Asked:
PKTG
2 Solutions
 
ZberteocCommented:
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
 
David ToddSenior DBACommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now