Post reply

text data reorg?

  • January 12, 2004 at 9:43 pm

    #177397

    Hello,

    We have a table that has over 60GBs allocated to it but the used space is just over 20GBs.  95% of this data is stored in a text field.  I'm guessing that the allocation of this much space is due to fragmentation within the text pages. 

    What are my options for reclaiming this space?  BCP, DTS?

    Thanks,

    Greg

  • January 13, 2004 at 7:11 am

    #489740

    I would suggest BCP.

    1. Backup the database

    2. Count data records with "select count(*) from YourTable"

    3. Export the data records using the command line utility bcp.exe. for example, with "bcp PRD..YourTable out c:\outputfile.out -n -CRAW -T"

    4. Truncate the table o with "truncate table YourTable"

    5. Import the data records with "bcp PRD..YourTable in c:\outputfile.out -n -CRAW -T"

    6. Count the data records with "select count(*) from YourTable"

  • January 13, 2004 at 12:55 pm

    #489840

    Thanks for the reply,

    Originally that's what I was planning to do, but I wonder if there may be less work involved if I use DTS.  With BCP I will need to drop all foreign key constraints before I truncate the table and in this particular database(SAP), there are many foreign key constraints that reference this table.  Let me know if this makes sense.

    1. Backup the database

    2. Create duplicate of table - tablename2

    3. Load data from tablename1 into tablename2

    4. Select Count(*) from both tables to compare

    5. Rename tablename1 to tablename_formerprod

    6. Rename tablename2 to tablename1

    7. Test app against new tablename1

    8. If all tests well, truncate tablename_formerprod, drop tablename_formerprod

    Thanks,

    Greg

  • January 13, 2004 at 1:42 pm

    #489848

    Nevermind.  I just tested my theory above.  It seems that there is no way around dropping the foreign key constraints or remapping them in the scenario I described above.  For some reason I was under the impression that I could perform the procedure above and the foreign keys would magically stay pointed to the original tablename.  It makes sense of course that they don't sense the original tablename doesn't exist after it is renamed and therefore the foreign key must point to the new tablename.  Thanks for the advice.   

    Greg

Viewing 4 posts - 1 through 4 (of 4 total)

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