Post reply

Filegroup full (NOT what you''re thinking)

  • May 7, 2005 at 11:09 am

    #86133

    Here is my error:

    Error: 1105, Severity: 17, State: 2

    Could not allocate space for object 'ITEM_200518' in database 'ImageFile' because the 'GROUP_200518' filegroup is full.

    1.  My diskspace is fine

    2.  My log is set to unrestricted growth, as are all my filegroups

    3.  My database is set to auto shrink

    The fun part is this ... when I stop and start the database engine then error magically goes away.  I've gotten this once before ... don't want to keep stopping by database, uptime is critical - natch.  I cannot duplicate this error.

    Let's also throw into the mix that while I'm trying to set up replication using an administrative share that my administrator cannot access it.  I've also tried using a non-administrative share - same thing.  I get the following errors in succession:  14150, 14151, and nothing else. 

    This database is at sp3a on windows 2003. 

    Short of a server rebuild, are there any suggestions?

    Thanks!

     

     

    😎

  • May 8, 2005 at 12:25 pm

    #557506

    Is the server very busy ? These may be a time-out for the OS when the space allocation cannot be completed within a reasonable amount of time. The definition of "reasonable" is the time out interval specified in the client connection which defaults to 30 seconds.

    One solution is set the file increase amount to a fixed size. For example, if set to the default of 10% then on a 10GB file, the increase would be 1Gb, resulting in intermitent time-outs. A more reasonable size would be on the order of 16 extents or 1Mb.

    Also, you can avoid the file size increase by increasing the space on a period basis.

    I also suggest that autoshrink be turned off.

    SQL = Scarcely Qualifies as a Language

  • May 9, 2005 at 6:15 am

    #557562

    I second Carl's suggestion about turning AutoShrink off... AutoShrink causes several problems not the least of which can be severe disk fragmentation and long wait times when it does need to regrow (especially in the Log file).  There's just no substitute for planning the size of the database, including, say, a year's worth of growth or more.  Backing up the database on a nightly basis will keep the log file from consuming the entire harddrive over time.

    Also, to further prevent severe fragmentation, I check my rather large (+60GB) databases on a weekly basis... if I see it's getting to be time for a little growth... I'll add a couple of GIG! during a relatively quiet time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 9, 2005 at 7:05 am

    #557573

    thanks Carl and Jeff ... your suggestions make sense.  hopefully i won't see that issue again!

    😎

  • May 9, 2005 at 7:08 am

    #557575

    Though auto grow by a fixed size is criticl to a big and busy database, I prefer to allocate Big Enough Space manually when the db is setup or during the planned down time. This can avoid file fragmentation due to auto growth. The perfromance will be better too for a busy system.

     

  • May 9, 2005 at 7:28 am

    #557580

    Also you might want to consider setting up a seperate filegroup for text data as oppossed to your primary filegroup. In SQL 7 there were known issues with recovery of space on file groups with text data which would show the space empty but nothing you did other than update the text could recover the space many times. There could be something similar going on in SQL 2000 for you. However do test the first suggestion and I suggest consider this only if you have no other solution you can find.

  • May 9, 2005 at 7:57 am

    #557585

    i'm already using separate filegroups for my text data - a new one every week.

    this is my first foray into working with very large, high volume databases.  so, this is my first post, probably will not be my last.  i THANK YOU  for you all for your quick and informative posts. 

    😎

  • June 22, 2005 at 1:19 am

    #567321

    Which edition of MS SQL Server you are using ? Is it a MSDE ?

    "The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower"

     

  • December 12, 2005 at 12:01 pm

    #609072

    not msde

    😎

Viewing 9 posts - 1 through 8 (of 8 total)

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