Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: strange behavior with MSSQL

    Hi,
    We are noticing some strange behavior with MSSQL. I was hoping somebody can shed some light.

    Since the past few days in our production database we have been getting the following error

    Could not allocate space for object 'Person' in database 'PROD' because the 'PRIMARY' filegroup is full...


    Some data on your system

    The PRIMARY filegroup is 20G in size. And 80% of it is free. Also, the Primary filegroup is setup to auto grow and there is about 20G free space at the OS level. So, I don't think it has anything to do with the filegroup.

    I started doing some research on the 'person' object (table), run sp_spaceused etc... to get some data. On a trail and error basics I run DBCC INDEXDEFRAG on the 'person' table and the error went away.

    Questions

    1. Why is the error misleading? Why does it say, the 'PRIMARY' filegroup is full?
    2. Why am I getting this error and why does running DBCC INDEXDEFRAG fix the problem?
    3. I can understand the index being fragmented and needing a defrag, but can MSSQL server actually fail with this error if the index is fragmented too much?
    4. What data can I look at and prevent this from happening in the future?

    Any other data will be much appreciated.

    Thanks so much.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    1. Why is the error misleading? Why does it say, the 'PRIMARY' filegroup is full?
    - The error is very specific,- there is a transaction (bulk copy, INSERT...SELECT FROM) that attempts to load data into this table. I assume you have a limit on file growth (if you don't, - SET IT!), and growth increment is in MB not in %, right? The amount of data attempted to be loaded exceeds the limit of growth and the transaction gets rolled back. I also assume your transaction log file is pretty big by now, unless you periodically shrink it.

    2. Why am I getting this error and why does running DBCC INDEXDEFRAG fix the problem?
    - It's a 2-part question, and answer for part 1 is above. I assume you have only 1 filegroup (PRIMARY), which means that your indexes are sitting on the same file as your data. INDEXDEFRAG reshuffles index pages and releases freed up pages back to allocated space of the data device, thus allowing for additional extent allocation for the table in question.

    3. I can understand the index being fragmented and needing a defrag, but can MSSQL server actually fail with this error if the index is fragmented too much?
    - The server is not failing, the transaction that initiated data load fails (rolls back).

    4. What data can I look at and prevent this from happening in the future?
    - Look into Scheduled Tasks and DTS packages. Most likely you troubles are coming from there (too many busy fingures )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    Thanks for your reply.

    1. The transaction that is failing is a simple insert/update statement that is inserting/updating a single row in the table. We don't have a limitation on file growth. We have the initial size of the datafile at 20G out of which 80% is free and I have growth increments set at 500MB. Transaction logs are backed up often and then shrunk. Given the facts above, I still don't understand why I am getting the error
    Could not allocate space for object 'Person' in database 'PROD' because the 'PRIMARY' filegroup is full...

    2. Even though the indexes are sitting on the same filegroup, if there is enough space in the filegroup and file growth is set to 500MB, why won't the table be able to extend and add more data?

    3. I still don't understand what's causing this error if there is enough space in the filegroup.

    4. It's a single insert that's causing this problem. Does this problem have to do with extending data in a index page and is not being able to or something like that. Maybe an update is failing?

    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did yu do DBCC CHECKTABLE or CHECKDB?

    Anything in the error logs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SQL Server WILL NOT throw this error if you had enough space in PRIMARY file group. You need to analyze the process and see if you missed something else there. UPDATE operation would result in the same situation if new data is larger (looks like significantly) than the existing data.

    The example may be stupid but possible to occur:

    set ansi_padding on
    create table tbl (f1 char(1000) null, f2 char(1000) null)
    go --at this point data=0KB
    insert tbl select 'a', 'b' union select null, null
    go --at this point data=16KB
    drop table tbl
    go

    With 50 rows of the same data the size will be 136KB.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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