Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: Page allocationError

    Hi all,
    There a quite strange error i am getting :
    "Could not allocate new page for database 'rough'. There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects, adding additional files, or allowing file growth."

    This database has unrestricted filegrowth, the current size of the datafile is 4.6GB and there is 9 GB space free on the hard disk.

    The recovery model is full logged.
    sql server 2k with sp3a.

    any thoughts?

    harshal.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Run SP_SPACEUSED & SP_HELPDB for information on this database.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Satya
    Run SP_SPACEUSED & SP_HELPDB for information on this database.

    what exactly should i be looking for?
    It shows 4081.94 MB space used. And unallocated space is shown negative i.e. -9.74 MB.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    CHeck what is the space left on PRIMARY FILEGROUP.
    Run SP_HELPFILE and use DBCC UPDATEUSAGE before running.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Hi satya,
    thanks for the reply.
    after running dbcc updateusage, it updated the usage.
    after running sp_helpfile it shows for primary filegroup size=4116480 KB
    Max=5121024 KB
    Growth=10%
    when i try to increase the size of the primary datafile it again gives the same error.

    thanks
    harshal.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you disabled auto grow on the primary filegroup?
    Ensure there are no disk space issues where Primary filegroup is hosted.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Satya
    Have you disabled auto grow on the primary filegroup?
    Ensure there are no disk space issues where Primary filegroup is hosted.
    That is the whole problem auto grow is enabled and there is ample space on the drive where primary filegroup is hosted, still it is not allocating the space.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    See what happens when you manually expand the file by say 100 MB. You may be running into a timing conflict of some kind, with the growth set to 10%. At this point, the file will try to grow by almost half a gig. This will take some time. I would consider setting the filegrowth to a flat value. Probably 10 - 50 MB, depending on the growth rate of the database.

  9. #9
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by MCrowley
    See what happens when you manually expand the file by say 100 MB. You may be running into a timing conflict of some kind, with the growth set to 10%. At this point, the file will try to grow by almost half a gig. This will take some time. I would consider setting the filegrowth to a flat value. Probably 10 - 50 MB, depending on the growth rate of the database.
    Ok I tried increasing the size of the database in units of 50 MB, it worked fine the first time when the size was 4095MB but after that if i try to increase it more even by 1 MB it gives me the same error.
    What can possibly be the reason? can there be some kind of quota or some thing which may be configured?

  10. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    How much available disk space do you have on that drive?
    That which does not kill me postpones the inevitable.

  11. #11
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Seppuku
    How much available disk space do you have on that drive?
    free space on the drive is 9.04 GB.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. Maybe there is an O/S limit on files? Never heard of such a thing, though. But, just to test out if it is the O/S playing with your head, try backing up the database a couple times to the same file. You should be able to create a file greater than 4GB in size. What O/S is this? 2K or 2K3?

  13. #13
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    ok i'll try that.
    its 2k server

  14. #14
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by harshal_in
    ok i'll try that.
    But only on MONDAY

    Time to call it a week !!!

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley
    Hmm. Maybe there is an O/S limit on files? Never heard of such a thing, though. But, just to test out if it is the O/S playing with your head, try backing up the database a couple times to the same file. You should be able to create a file greater than 4GB in size. What O/S is this? 2K or 2K3?
    Don't even go there! The SQL Server limits aren't an issue here!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •