"Primary Filegroup is Full" in SQL Server 2008 Standard for no apparent reason

Question

Our database is currently at 64 Gb and one of our apps started to fail with the following error:

System.Data.SqlClient.SqlException: Could not allocate space for object 'cnv.LoggedUnpreparedSpos'.'PK_LoggedUnpreparedSpos' in database 'travelgateway' 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.

I double-checked everything: all files in a single filegroup are allowed to autogrow with a reasonable increments (100 Mb for a data file, 10% for a log file), more than 100 Gb of free space is available for the database, tempdb is set to autogrow as well with plenty of free HDD space on its drive.

To resolve a problem, I added second file to the filegroup and the error has gone. But I feel uneasy about this whole situation.

Where' the problem here, guys?

And there is no Maximum File Size specified ? – Damir Sudarevic Dec 23 '09 at 13:34 — Damir Sudarevic, Dec 23 '09 at 13:34

score 28 · Accepted Answer · edited Apr 05 '17 at 14:16

28

OK, got it working. Turns out that an NTFS volume where the DB files were located got heavily fragmented. Stopped SQL Server, defragmented the whole thing and all it was fine ever since.

edited Apr 05 '17 at 14:16

A-Sharabiani

17,750
17
113
128

answered Mar 26 '10 at 08:09

Anton Gogolev

113,561
39
200
288

Thanks, woke up to having to solve the same problem today and defragging the data file (.mdf) fixed it. I used Defraggler. – dtbarne Jun 13 '11 at 14:54
I had the same problem and after running defrag on the disk the data file was stored, it was solved! Thanks. – Erick Sasse Jun 24 '11 at 23:04

score 19 · Answer 2 · answered Dec 25 '09 at 05:30

Anton,

As a best practice one should n't create user objects in the primary filegroup. When you have bandwidth, create a new file group and move the user objects and leave the system objects in primary.

The following queries will help you identify the space used in each file and the top tables that have highest number of rows and if there are any heaps. Its a good starting point to investigate this issue.

SELECT  
ds.name as filegroupname
, df.name AS 'FileName' 
, physical_name AS 'PhysicalName'
, size/128 AS 'TotalSizeinMB'
, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB' 
, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  
    ON df.data_space_id = ds.data_space_id;

EXEC xp_fixeddrives
select  t.name as TableName,  
    i.name as IndexName, 
    p.rows as Rows
from sys.filegroups fg (nolock) join sys.database_files df (nolock)
    on fg.data_space_id = df.data_space_id join sys.indexes i (nolock) 
    on df.data_space_id = i.data_space_id join sys.tables t (nolock)
    on i.object_id = t.object_id join sys.partitions p (nolock)
on t.object_id = p.object_id and i.index_id = p.index_id  
where fg.name = 'PRIMARY' and t.type = 'U'  
order by rows desc
select  t.name as TableName,  
    i.name as IndexName, 
    p.rows as Rows
from sys.filegroups fg (nolock) join sys.database_files df (nolock)
    on fg.data_space_id = df.data_space_id join sys.indexes i (nolock) 
    on df.data_space_id = i.data_space_id join sys.tables t (nolock)
    on i.object_id = t.object_id join sys.partitions p (nolock)
on t.object_id = p.object_id and i.index_id = p.index_id  
where fg.name = 'PRIMARY' and t.type = 'U' and i.index_id = 0 
order by rows desc

Wonderful script. One of the problems which I ran into is "size/128" integerized the calculation which caused a division by zero error. Changed it to "size/128." — Dave, Jul 05 '13 at 16:39
This script does not work for me. I get some rows affected and then an error - Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. — Steam, Feb 06 '14 at 19:18

Hugo Delsing · Answer 3 · 2017-06-14T08:43:15.020

19

Ran into the same problem and at first defragmenting seemed to work. But it was for just a short while. Turns out the server the customer was using, was running the Express version and that has a licensing limit of about 10gb.

So even though the size was set to "unlimited", it wasn't.

edited Jun 14 '17 at 08:43

answered Jan 23 '15 at 13:36

Hugo Delsing

13,803
5
45
72

score 4 · Answer 4 · edited Nov 14 '14 at 10:05

4

Do one thing, go to properties of database select files and increase initial size of database and set primary filegroup as autoincremented. Restart sql server.

You will be able to use database as earlier.

edited Nov 14 '14 at 10:05

Robert

5,278
43
65
115

answered Nov 14 '14 at 10:00

vishal walujwar

61
1

Yahoo Serious · Answer 5 · 2022-10-15T12:40:08.400

In my (probably rare) case, I made a new TrueCrypt-partition/file, amd chose to store files larger than 4 GB, which probably made it use an exfat file system. (See details (and other attempts) below.)

TLDR

I also ran into the same problem, where the initial dtabase size is set to 4Gb and autogrowth is set by 1Mb. The virtual encrypted TrueCrypt drive that the databse was on, seemed to have plenty of space.

I changed a couple of (the above) things:

I turned the Windows service for Sql Server Express from automatic to manual, so only the 'regular' Sql Server is running. (Even though I am running Sql Server 2008 R2 which should allow 10 GB.)
I changed the autogrowth from 1 MB to 10%
I changed the autogrowth increment-size from 10% to 1000 MB
I defragmented the drive
I shrank the database:
- manually DBCC SHRINKDATABASE('...')
- automatically right click on database | "properties" | "Auto Shrink" | "Truncate log on check point")

All to little avail (I could insert some more records, but soon ran into the same problem). The pagefile mentioned by Tobbi, made me try a larger virtual drive. (Even though my drive should not contain any such system files, since I run without it being mounted a lot of the time.)

I made a new larger virtual drive with TrueCrypt

When making this, I ran into a TrueCrypt-question, if I am going to store files larger than 4gb (as shown in this SuperUser question).

I told TrueCrypt I would store files larger than 4 GB

After these last two I was doing fine, and I am assuming this last one did the trick. I think TrueCrypt chooses an exfat file system, which limits all files to 4GB. (So I probably did not need to enlarge the drive after all, but I did anyway.) See veracrypt-file-system-option-for-larger-files for a nice list of file-system formats with pros and cons.

This is probably a very rare border case, but maybe it is of help to somebody.

score 2 · Answer 6 · answered Jul 18 '12 at 23:29

I just ran into the same problem. The reason was that the virtual memory file "pagefile.sys" was located on the same drive as our data files for our databases (D: drive). It had doubled in size and filled the disk but windows wasn't picking it up, i.e. it looked like we had 80 GB free when we actually didn't.

Restarting SQL server didn't help, perhaps defragment would give the OS time to free up the pagefile, but we just rebooted the server and voila, the pagefile had shrunk and everything worked fine.

What is interesting is that during the 30 min we were investigating, windows didn't calculate the size of the pagefile.sys at all (80gb). After restart windows did find the pagefile and included it's size in the total disk usage (now 40gb - which is still too big).

score 2 · Answer 7 · answered Jan 10 '13 at 08:17

2

please chceck the type of file growth of the database, if its restricted make it unrestricted

answered Jan 10 '13 at 08:17

annonymous

21
1

score 2 · Answer 8 · edited Feb 21 '14 at 17:15

I found that this happens because: http://support.microsoft.com/kb/913399

SQL Server only releases all the pages that a heap table uses when the following conditions are true: A deletion on this table occurs. A table-level lock is being held. Note A heap table is any table that is not associated with a clustered index.

If pages are not deallocated, other objects in the database cannot reuse the pages.

However, when you enable a row versioning-based isolation level in a SQL Server 2005 database, pages cannot be released even if a table-level lock is being held.

Microsoft's solution: http://support.microsoft.com/kb/913399

To work around this problem, use one of the following methods: Include a TABLOCK hint in the DELETE statement if a row versioning-based isolation level is not enabled. For example, use a statement that is similar to the following:

DELETE FROM TableName WITH (TABLOCK)

Note represents the name of the table. Use the TRUNCATE TABLE statement if you want to delete all the records in the table. For example, use a statement that is similar to the following:

TRUNCATE TABLE TableName

Create a clustered index on a column of the table. For more information about how to create a clustered index on a table, see the "Creating a Clustered Index" topic in SQL

You'll notice at the bottom of the link that it is NOT noted that it applies to SQL Server 2008 but I think it does

score 1 · Answer 9 · answered Jun 01 '19 at 01:14

In my experience, this message occurs when the primary file (.mdf) has no space to save the metadata of the database. This file include the system tables and they only save their data into it.

Make some space in the file and the commands works again. That's all, Enjoy

score 0 · Answer 10 · answered Oct 21 '16 at 18:25

0

our problem was that the hard drive was down to zero space available.

answered Oct 21 '16 at 18:25

batman

11

"Primary Filegroup is Full" in SQL Server 2008 Standard for no apparent reason

10 Answers10

TLDR

Linked