Space Error

  • Hi Experts,

    I got error on one of our production database.

    Could not allocate space for object 'dbo.'.'' in database '' 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 database got 3 files in the Primary filegroup and two are restricted in size but the third file is newly created in a disk with enough space to grow(attached screesnshot). Still why this error?

     

    TIA

     

     

    Attachments:
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • The first two are not full but don't have room to expand by the specified amount.

    Alter the first two files to have a maxsize of their current size.  That will prevent SQL from attempting to add space to either of those files.

    ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_1], MAXSIZE = 389294MB )

    ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_2], MAXSIZE = 225792MB )

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    The first two are not full but don't have room to expand by the specified amount.

    Alter the first two files to have a maxsize of their current size.  That will prevent SQL from attempting to add space to either of those files.

    ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_1], MAXSIZE = 389294MB )

    ALTER DATABASE [your_db_name] MODIFY FILE ( NAME = [your_file_name_2], MAXSIZE = 225792MB )

     

    Thanks Scott.  Will the operation fail because of the existing setup or it will try using the new file?

  • Should use the new file just fine.  Make sure the new file is present, or you will have no space to write to.

    What's happening is this.  Your file has not reached its max, so SQL tries to grow the file .., but the amount of disk remaining on that drive is not enough to grow as much as you specified, so the growth fails.

    By setting the max size of the file to its current size, SQL will never try to grow that file, since it has reached its max size, avoiding this error.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    Should use the new file just fine.  Make sure the new file is present, or you will have no space to write to.

    What's happening is this.  Your file has not reached its max, so SQL tries to grow the file .., but the amount of disk remaining on that drive is not enough to grow as much as you specified, so the growth fails.

    By setting the max size of the file to its current size, SQL will never try to grow that file, since it has reached its max size, avoiding this error.

    Thanks Scott, I think I didn't make my question clear. If I didn't set the file size as you suggested will the transaction fail after the error or it will succeed by using the available file.

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply