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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  as described here   which limits all files to 4GB    So I probably did not need to enlarge the drive after all  but I did anyway    This is probably a very rare border case  but maybe it is of help to somebody

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server-2008] "Primary Filegroup is Full" in SQL Server 2008 Standard for no apparent reason

Examples related to sql-server-2008

Examples related to filegroup