Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Unanswered: Release Unused Space reserved for a table

    Hey guys,

    I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..

    Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    what's wrong with

    ALTER TABLE tbla DROP COLUMN clma

    Can you not just Drop Text columns ? if not then how about using a SELECT INTO to create another table without the specified column

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    I can drop the column, but that doesn't change anything..

    I then run DBCC CLEANTABLE.. And the result of sp_spaceused changes.. The unused space increases..

    But untimately I want to shrink the actual reserved size of the table so in the end i can shrink that entire database to free up disk space..

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    according to BOL
    DBCC CLEANTABLE reclaims space after a variable length column or a text column is dropped using the ALTER TABLE DROP COLUMN statement. It does not reclaim space after a fixed length column is dropped.

    However This operation is fully logged, maybe you need to Dump/Backup the transaction Log.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    True thats what BOL says...

    However I have 6 text fields in the table.. as all these are stored in the same place (index 255), i am thinking that i have to drop all text fields before CLEANTABLE works.. Well thats whay i am seeing anyway.. Maybe SQL server doesnt handle text fields as good as it should?

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Mmmmmm now we're downt to the nitty gritty if BOL is lying to us.

    I hate Text fields anyway - is'nt the actual data held outside the table n the Row actually only records pointers to it.

    Consider sp_tableoption & text in row option.

    Sorry I have no easy answer other than a SELECT INTO to create then delete original then rename the new table.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, text data is not held in the table. The table stores pointers to the text data.
    I think you are barking up the wrong tree wanting to reclaim "table" space. You have already dropped the column and stated that the unused space increased. So now you need to shrink your database. Be advised that this is generally frowned upon, since presumably the database will eventually expand to the same size again anyway, and it will require system resources to do so.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    You are right blindman, the db will increase to the same size again... But wont get there for approximately 12 months and I have some immediate space issues (hard disk) that I am trying to solve (or at least delay)..

    Let me try and set up an example.. Lets say I have a database with 1 table and the sp_spaceused looks like below:

    name rows res data index unused
    tblaaa 1000 100392 KB 25360 KB 16 KB 75016 KB

    I can't shrink the database down to 30Mb as this table still has 100Mb reserved for it.. How can I shrink the database to 30Mb without bcping out all the data and truncating the table? Is it possible?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think you can shrink a database below the original size of its files when it was first created. You would have to create a new database from script and then transfer your data into it.
    But buddy, hard drives are CHEAP!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Yes, you can shrink the database or files individually. In the enterprise manager... shrink database window, there is a button for shrink files that allows you to shrink individual data (log) files further. Please backup your database before you try this. You can also use DBCC SHRINKFILE.... if you prefer ISQL...
    Last edited by skrishnamurthy; 09-23-05 at 16:13.

  11. #11
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Quote Originally Posted by blindman
    I don't think you can shrink a database below the original size of its files when it was first created. You would have to create a new database from script and then transfer your data into it.
    But buddy, hard drives are CHEAP!

    Correct again. you can't shrink a database smaller than it was created.. But im not trying to.. unfortunatly buying hard drives is out of my control, they are coming.. but in time

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Heck, I've got a couple of hard drives down in my basement. Come on over and get 'em. In the meantime, maybe you could temporarily archive some information to a CD. Best of luck.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Haha.. No worries.. I'll swing past and pick up those drives around 8? lol

    Thanks mate.

Posting Permissions

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