"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?

Answer 1

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 =P

Hope this helps

Answer 2

Hmmmm, quite an odd error, given the circumstances. I am not sure if the answer by ajdams would directly cause this, if it does very interesting.

Do you know what operation was run to cause this? The only thing I can think of would be if the database needed to reserve a very large amount of disk space for a transaction, beyond the 10GB you had available (off the top of my head I believe SQL will reserve space for the operation, freeing it up afterwards).

The operation would probably not be a standard query, maybe an alter on a clustered index?

Answer 3

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

Answer 4

OK, got it working. Turns out that a 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.