Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70

    Unanswered: Unused Space issue

    Hi,

    My database’s actual size is 3+ GB, but due to this Unused (Reserved) space it currently shows as 12+ GB and grows at a rate of 150MB per every 3 hrs.
    When I check the space usage by Individual tables, I saw a table <xxx> having reserved space of around 8 GB which contains only 2 columns of which one is an integer (primary key, clustered index) and the other is an image data type (BLOB) field.
    The image size would be some KB or to a max of 1-2MB and once it is read, it gets automatically deleted from the table.
    There are currently less than 20 rows in the table.

    Is there any way to get rid of this unused space other than deleting and recreating the table, cannot be done as it’s a prod environment (24*7).

    Any help on this is highly appreciated.

    Thanks,
    Pavan.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by pavan_kashyap
    Is there any way to get rid of this unused space other than deleting and recreating the table, cannot be done as it’s a prod environment (24*7).
    Wait until the disk fills up and your DBMS grinds to a halt, and in the ensuing panic, quietly delete and recreate the table.

  3. #3
    Join Date
    Sep 2005
    Posts
    161

  4. #4
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    I have seen this earlier but I hope it doesnt, all the para's are correct.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    drop and recreate the clustered index. of course for that you will have to drop the foreign key in dependent tables first. after constraints are dropped, drop the primary key, create a new one, but make it non-clustered. then create a new clustered index. having PK non-clustered allows you in the future to perform this "reclaiming" of data without going through dropping and recreating foreign keys on other tables.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    but is that going to help me in reclaiming my unused space.
    Anyway, that table doesnt have any foreign keys.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    yes it will, and since there are no foreign keys, just drop the PK and create a new one as non-clustered. then create a new clustered index on the same column. Next time all you will have to do is rebuild your clustered index.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Thank you very much, it helped me a lot, it worked fine in my test environment without any issues, but in prod it gave me few tests and finally worked out as success.
    Do we need to truncate the table before we proceed???

    Once again, thank you very much for all your help.
    Pavan.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    no table truncation needed.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    The table has grown again and when I try to rebuild the index its not working, and the size remains the same again

  11. #11
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Any suggestions, how to proceed..??

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Either you're missing something, or the table size reported truly reflects the amount of data the table holds.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    My code is like this:
    CREATE TABLE [dbo].[link_tag](
    [link_id] [int] NOT NULL,
    [datamatrix_image] [image] NULL,
    CONSTRAINT [PK_link_tag] PRIMARY KEY NONCLUSTERED
    (
    [link_id] ASC
    ))
    And then add a clustered index.

Posting Permissions

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