none
ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in primary filegroup RRS feed

  • Question

  • Hello,

    I am getting this error on my server; SQL Server 2005 Enterprise Edition. Available space on my hard disk is 107 GB and log file is at other location. The mdf file for this database is of size 153 GB.

    So as lot of solutions to this problem say to set the file size to autogrowth because I have sufficient space as of now (107 GB).

    But I am not sure about the consequences and also by how much size I should allow the autogrowth?

    Any urgent help on this will be highly appreciated.

     

    Regards,

     


    In persuit of learning SQL.
    Friday, August 26, 2011 4:14 AM

Answers

  • Hi

    AutoGrowth 10% would be the ideal option.

    You can consider the autogrowth performance based on this link.

    http://support.microsoft.com/kb/315512

     


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

     


    I would disagree with 10% growth. Since its in percentage, every growth would be bigger in size.. Assume that db size is 100 GB, it would grow 10 GB (which is high)  and it would become 110 GB and next growth would be 11 GB.. and it increases exponentially. Filegrowth time would be included in response time by application.

    I would suggest to keep fix growth of 512 MB.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Abhay_78 Friday, August 26, 2011 6:15 AM
    • Marked as answer by Peja Tao Thursday, September 1, 2011 8:14 AM
    Friday, August 26, 2011 4:43 AM
    Moderator
  • I would agree also - however, I disagree on the fixed size of 512MB.  The fixed size for growth should be a factor of your daily growth and allow enough time for you to schedule a manual growth to allow for several months of growth.

    For example, if your daily growth is 500MB per day - an autogrowth of 512MB will only cover a single days activities.  If you happen to be on vacation for 2 weeks - and this occurs on the first day of your vacation, your users will be affected by the autogrowth every day until you get back.

    You want to also enable instant file initialization - this will reduce the impact of the autogrowth event on the system.

    To give you an idea, on my 1.3TB system - with an average daily growth of almost 700MB, I have an autogrowth setting of 20000MB.  If the system were to hit this, with instant initialization enabled - the autogrowth only takes a couple of seconds.  Of course, this is because of the IO subsystem, the SAN we are using and other factors.  I would not recommend a size that large unless you have tested your IO subsystem, your SAN and the system.


    Jeff Williams
    • Proposed as answer by Peja Tao Monday, August 29, 2011 8:27 AM
    • Marked as answer by Peja Tao Thursday, September 1, 2011 8:14 AM
    Saturday, August 27, 2011 2:16 AM

All replies

  • Hi

    AutoGrowth 10% would be the ideal option.

    You can consider the autogrowth performance based on this link.

    http://support.microsoft.com/kb/315512


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

    Friday, August 26, 2011 4:25 AM
  • Hi

    First try to determine the average growth of the table per day in file size and restrict the growth of the file to fixed size and with specific File size increment and not the default percentage increment.

    The consequence of setting the Unrestricted growth is when it reaches the maximum limit all the exiting connections to the database will hang and no further transaction would be allowed.

    The best approach is to allow the database to grow in fixed size and create alerts for disk usage so that you would get an idea about how fast the database is growing and take counter measures to get more space (or) to archive some data from the database to gain space.

    You might also need to see the file growth property of each and every individual file in your primary file group first and ensure the file growth pattern is set to a fixed size and not by percentage and to a specific maximum size.

    There is another rule that if the Data occupies more than 80% of the total space of the disk you will certainly see a disk performance. If in case the database is supporting OLTP applicaiton whith lot of delete , insert and update which might also lead to disk fragmentation.

    Considering these factors it is better to set the database growh to fixed size and do a regular Monitoring activity on the database and the disk size.

     

     

     


    MCTS/MCITP (SQL Server 2005, 2008, 2005 BI)
    • Proposed as answer by david_mgopal Friday, August 26, 2011 4:32 AM
    • Unproposed as answer by david_mgopal Friday, August 26, 2011 4:43 AM
    Friday, August 26, 2011 4:32 AM
  • Hi

    AutoGrowth 10% would be the ideal option.

    You can consider the autogrowth performance based on this link.

    http://support.microsoft.com/kb/315512

     


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

     


    I would disagree with 10% growth. Since its in percentage, every growth would be bigger in size.. Assume that db size is 100 GB, it would grow 10 GB (which is high)  and it would become 110 GB and next growth would be 11 GB.. and it increases exponentially. Filegrowth time would be included in response time by application.

    I would suggest to keep fix growth of 512 MB.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Abhay_78 Friday, August 26, 2011 6:15 AM
    • Marked as answer by Peja Tao Thursday, September 1, 2011 8:14 AM
    Friday, August 26, 2011 4:43 AM
    Moderator
  • I agree and you might end up getting latch timeout becuse of high log growth which is again due to percentage setup ..I hav eseen this multiple times (also depends on how strong your IO sub system is .Sometimes it comes early sometimes late ).
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, August 26, 2011 6:17 AM
  • I would agree also - however, I disagree on the fixed size of 512MB.  The fixed size for growth should be a factor of your daily growth and allow enough time for you to schedule a manual growth to allow for several months of growth.

    For example, if your daily growth is 500MB per day - an autogrowth of 512MB will only cover a single days activities.  If you happen to be on vacation for 2 weeks - and this occurs on the first day of your vacation, your users will be affected by the autogrowth every day until you get back.

    You want to also enable instant file initialization - this will reduce the impact of the autogrowth event on the system.

    To give you an idea, on my 1.3TB system - with an average daily growth of almost 700MB, I have an autogrowth setting of 20000MB.  If the system were to hit this, with instant initialization enabled - the autogrowth only takes a couple of seconds.  Of course, this is because of the IO subsystem, the SAN we are using and other factors.  I would not recommend a size that large unless you have tested your IO subsystem, your SAN and the system.


    Jeff Williams
    • Proposed as answer by Peja Tao Monday, August 29, 2011 8:27 AM
    • Marked as answer by Peja Tao Thursday, September 1, 2011 8:14 AM
    Saturday, August 27, 2011 2:16 AM
  • I agrer to the point that the default growth percentage should not be used which is the same which i have higlighted in my previous review.

    "First try to determine the average growth of the table per day in file size and restrict the growth of the file to fixed size and with specific File size increment and not the default percentage increment."

     

    There is one more thing we need to remember that when a huge file growth is set the SQL Server storage engine will make a request to the disk controller to identify the free blocks and block them.

    Now all our disk uses  Random allocation in the disk and not sequential.

    Hence  if the default file growth is very huge and the same Disk controller serves the production application think about the delay we are going to face in acquiring the huge free space and one more thing that you would see is that current exisitng connection agains the database would be slow some time even get timed out because of huge file growth activity that is going on at the back ground,

    There is a very good book exist to discuss about the  disk performance issues and various DBA ACTIVITY If not planned well which might affect the DB.

    If you get a chance please refer to Chapter 3 and Chapter 4 in the book"SQL Server 2000 Performance Tuning" Author : Edward whalen,Marcillina Garcia,Steve drien Deluca and Dean thomson.

    Note: This is not specific to SQL 2000 these chapters discuss about deriving a formula for identifying the Disk bottlenecks

     

     

     

     

     


    MCTS/MCITP (SQL Server 2005, 2008, 2005 BI)
    Sunday, August 28, 2011 12:58 AM
  • Hi,

    Various factors we need to consider while deciding on the FILE GROWTH option, they are
    1. Database present size
    2. Expected growth of database
    3. File growth option i.e in percent or in MB
    4. MAXSIZE
    5. DISK size
    6. Need to take consideration of other database on the server sitting on same disk
    7. Other usage of disk, may be windows admins/web admins might be using that disk

    After answering above questions we can set the AUTOGROWTH options

    We can either select percent growth or growth in MB, both works fine, I always prefer to use growth in MB rather than going with percent.

    We need give more importance for MAXSIZE parameter, if we set to UNRESTRICTED growth then it will grow till your disk fills, which may open new problem, so better idea is to put a cap on GROWTH.

    To set some alerting mechanism on file growth, set up the alert to fire when db size grows to 80%

    Another Solution is adding new file to PRIMARY filegroup on a disk where we have enough disk space.


    Regards,
    SQLforU
    info@sqlforu.com
    ------------------------------------------------------------
    For all SQL Server/Sybase Online training,
    please conatct
    contact@sqlforu.com
    www.sqlforu.com
    ------------------------------------------------------------
    Sunday, August 28, 2011 6:49 PM