Autogrowth On But Database Not Growing

ben himself's picture

An issue arose where a database was giving the error:

Could not allocate space for object ‘object-name’ in database ‘dbname’ 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.


The issue was that there was still ~40GB free on the drive and autogrowth was turned on for all of the files in the filegroup. There should be nothing preventing a file growth. And yet, it would not grow.

The problem, apparently, was that SQL Server became confused  with how much space was actually available.  This simple script fixed the problem:

-- Documentation: http://technet.microsoft.com/en-us/library/ms188776.aspx
USE dbname
GO
sp_spaceused @updateusage = N'TRUE';
GO

When “updateusage” is set to TRUE, it runs DBCC UPDATEUSAGE on the database selected. This corrects SQL’s apparent internal conflict. Please note, it is not fast. Obviously, the database size and processing power will impact the length of time it runs. Don’t be surprised if it runs for between 15 and 30 minutes, or longer for extremely large databases.

Tags: 

Categories: