locked
Urgent:Could not allocate a new page for database 'Mydatabase' because of insufficient disk space in filegroup 'PRIMARY'. RRS feed

  • Question

  • i got the below error

    ERRor :Could not allocate a new page for database 'Mydatabase' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    when i click on database properties->files

    i have four files

    out of which 1 is data file mdf   and auto growth is set By 100 MB, restricted growth to 126172 MB

    2 ndf files   autogrowth for one ndf file By 100 MB, restricted growth to 100000 MB and other ndf with auotgrowth By 100 MB, restricted growth to 133120 MB

    and 1 ldf By 10 percent, restricted growth to 75000 MB

    by the above error to which of mdf or ndf file to i increase the diskspace

    plse tell me what should i do

    Sunday, September 30, 2012 1:09 AM

Answers

  • Your settings are using autogrow, but you have set a limit to the size in which it can continue to grow (autogrow up to 126172).  You are hitting this limit now and there is not room for an additional autogrow.

    In order to autogrow this filegroup based on your settings, you need to have 100MB available and you only have 34kb since you have the max size set to 126172.  Since the current size is 126138, you are nearly at the max value.  You'll need to either remove or increase the max cap, but you need to look at the disk space and expected growth of the DB in order to make these decisions.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Sunday, September 30, 2012 2:54 AM
  • The error message is telling you that the filegroup named "PRIMARY" is out of space.  It is likely your .mdf file, but to check, right-click on the DB name in SSMS and view the properties.  Click on the Filegroup link and find the file associated with the filegroup named PRIMARY.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by LuckyDba Sunday, September 30, 2012 3:02 AM
    Sunday, September 30, 2012 1:42 AM

All replies

  • The error message is telling you that the filegroup named "PRIMARY" is out of space.  It is likely your .mdf file, but to check, right-click on the DB name in SSMS and view the properties.  Click on the Filegroup link and find the file associated with the filegroup named PRIMARY.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by LuckyDba Sunday, September 30, 2012 3:02 AM
    Sunday, September 30, 2012 1:42 AM
  • Thank you sam lester.

    when i run sp_space used on 'Mydatabase' i see the below statistics

    space used :126137  

    Database file properties:

    initial db size 126138,autogrowth by 100mb up to 126172

    please tell me why couldnt it allocate a page when there is an auto growth property set

    here are more details about the database

     reserved                data             index_size           unused
    314037672 KB   281421392 KB    32454664 KB      161616 KB

    database_name   database_size    unallocated space
    Mydatabase          387421.44 MB     52560.09 MB

    Sunday, September 30, 2012 2:11 AM
  • Your settings are using autogrow, but you have set a limit to the size in which it can continue to grow (autogrow up to 126172).  You are hitting this limit now and there is not room for an additional autogrow.

    In order to autogrow this filegroup based on your settings, you need to have 100MB available and you only have 34kb since you have the max size set to 126172.  Since the current size is 126138, you are nearly at the max value.  You'll need to either remove or increase the max cap, but you need to look at the disk space and expected growth of the DB in order to make these decisions.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Sunday, September 30, 2012 2:54 AM
  • Thank you so much sam lester
    Sunday, September 30, 2012 3:01 AM