Could not allocate space

  • I am getting following error frequently... Has anyone faced similar issue and resolved it....

    Could not allocate space for object 'dbo.EventLog'.'PK__EventLog__71957DB0' in database 'eventlog' 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.

    Note: 1) I have sufficient space on drive

    2) Autogrowth is enabled

    3) I can add another file to filegroup and resolve the problem temporarily. After

    some days I am getting similar problem. Till now I have added 4 files to primary

    filegroup.

  • Hi,

    Can you provide some additional details please.

    How much free space do you have on the drive that the Primary Filegroup resides on?

    What are the size of the the data files that reside within the primary filegroup?

    What are your settings for autogrow for each data file i.e. is it a percentage or a fixed size?

    The reason I ask is, for example, you may have 3GB free disk space but if you have set autogrow to 5000MB then there would not be enough space to perform the operation.

  • Hi,

    I have 500 GB free space on the drive, on which primary filegroup resides. Size of the files are as given below

    1st Data File 69223 MB

    2nd Data File 43600 MB

    3rd Data File 108544 MB

    4th Data File 127400 MB

    Log File 5000 MB

    All are set with Autogrowth of 1024 MB. In fact I am unable to manually increase size of data file. It is giving following error If I am trying to expand any of the data file...

    MODIFY FILE encountered operating system error 1450 (Insufficient system resource exist to complete the requested service.)while attempting to expand the physical file. (Microsoft SQL Server, Error:5149)

    If I am adding new file on same drive to same filegroup, it is working fine.

  • Hi,

    Thanks for the additional details.

    I have a few more questions if I may.

    To confirm, are all of your data files stored within the Primary File Group?

    Which data file are you trying to increase the size of?

    How are you trying to increase the size of a given data file? Using SSMS or T-SQL (if so can you post the code)

    Cheers,

  • Sorry, I could not reply immediately...

    1. All files are in primary filegroup

    2. When I am trying to increase file size for any primary data file, it is giving following error

    Alter failed for Database 'eventlog'. (Microsoft.SqlServer.Smo)

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file. (Microsoft SQL Server, Error: 5149)

    3. When I am trying thru quey using alter database modify file it is giving following error...

    Msg 5149, Level 16, State 1, Line 1

    MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file.

  • Hi Nitin,

    please find out on which drives that database files resides for which you are having issues, check the available free space for that drive/drives.

    If there is ample space, What operation you are trying to do, so that you are getting this error message.

  • Which version and Edition of SQL Server you are using??

  • Hi Ratheesh/Murthykalyani,

    I am using SQL Server 2005, Enterprise Edition and Service pack is 2.

    The drive on which database resides is having ample space. But when the system tries to auto expand data file it is giving following error.

    Could not allocate space for object 'dbo.EventLog'.'PK__EventLog__71957DB0' in database 'eventlog' 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.

    Hence, if I am trying to manually expand any of the data files, I am getting error mentioned in my earlier message.

    Note: I have tried changing auto growth to 25 MB/ 100 MB/ 500 MB/1 GB but it is giving same error.

  • I am getting following error frequently... Has anyone faced similar issue and resolved it....

    Could not allocate space for object 'dbo.EventLog'.'PK__EventLog__71957DB0' in database 'eventlog' 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.

    Note: 1) I have sufficient space on drive

    2) Autogrowth is enabled

    3) I can add another file to filegroup and resolve the problem temporarily. After

    some days I am getting similar problem. Till now I have added 4 files to primary

    filegroup.

    Check the following and verfiy it.

    SELECT * FROM SYS.SYSALTFILES

    WHERE DBID=DB_ID('Db_name')

    XP_FIXEDDRIVES---NOTE (FREE SPACE IN MB NOT GB)

    Also See the Link and check Instant initialization enabled or not http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/53957409-4e41-415b-acfc-9fb13e4e8b70/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • 1) Checked this query " SELECT * FROM SYS.SYSALTFILES WHERE DBID=DB_ID('Db_name') " It is showing 1 GB Growth, maxsize unlimited

    2) After running XP_FIXEDDRIVES it is showing Free Space on Drive is 522293 MB

    3) Instant initialization is enabled

  • 1) Checked this query " SELECT * FROM SYS.SYSALTFILES WHERE DBID=DB_ID('Db_name') " It is showing 1 GB Growth, maxsize unlimited

    2) After running XP_FIXEDDRIVES it is showing Free Space on Drive is 522293 MB

    3) Instant initialization is enabled

    Hi,

    Please check the physical file fragmentation and Instant initialization enabled correct account or not.

    1.Right click my computer-->manage-->Disk defragmenter

    -->right side volume(ur physical drive)-->Click Analyze-->View the Report

    See the KB articel OS error :1450 http://support.microsoft.com/kb/967351

    2.Run-->secpol.msc-->Local policies-->User rights assignment

    -->Perform volume maintenance tasks

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Hi,

    I have checked as per your suggestion ...

    1. There is fragmentation, I will defragment the volume and post the result.

    2. Perform volume maintenance task permission is given to Administrators & Local service. Whereas SQL Services runs under Local system service account.

  • nitin.doshi,

    Did you find a solution to this error? I am getting the same error message.

    Exact error listed below.

    SQL Server 2005

    Microsoft SQL Server Standard Edition

    9.00.4211.00

    Memory: 26623 (MB)

    Processors: 8

    Server

    IBM x3650, 26GB memory

    OS: Windows 2003 Enterprise 32bit

    Database on the D: drive of the server.

    D: Drive is 1.3TB total capacity

    Database size: 506122.19 MB

    Get this error while trying to expand/grow the database. Have tried multiple growth sizes, such as 1mb, 500mb, etc.. All generate this error message.

    --------------------

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Alter failed for Database 'KMT_SEA_2008_EDART'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4211.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file. (Microsoft SQL Server, Error: 5149)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4211&EvtSrc=MSSQLServer&EvtID=5149&LinkId=20476

Viewing 13 posts - 1 through 12 (of 12 total)

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