Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Archive off large amount of nText Data

    Hi Guys

    SQL 2005 Enterprise

    Say I have an OLTP database with a table that has a application generated Clustered Primary Key (VarChar(16))

    one of the columns in the table is an (out of row) nText Column holding XML data so sometimes over 8k

    Table has around 25million rows and I want to write out/archive off the contents of around 5million of the rows (nText column) to a .txt file and then delete the contents of the archived nText columns (possible option of removing entire row).

    selection is based on a datetime column which is only roughly in order with the Primary Key.

    Currently simply extracting the 5million nText data to file is taking 10 hours with SSIS & I only have a 2 Hour window.

    I can't make any Schema changes as the DB is already highly optimized for Transaction Processing with Multiple Files/FileGroups on different spindles on the SAN

    Has anyone got any hints/tips/advice?

    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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Usually I am always trying to determine the rate of growth for a specific table, especially if it contains BLOB data (or CLOB, or SNOB, or whatever). Apparently this was not done when storage optimization techniques were being applied (creation of multi-file filegroups, etc.) Without partitioning, and with my limited creativity, you won't get away with it without unloading the data that you don't need (and along with that maybe even the data that you need) elsewhere. Of course, you can try dropping and creating a clustered index by placing it onto a partitioned schema that would draw a line between needed and unneeded data, and once you're done - drop the cl index. But, it most likely take you as long if not longer than BCP-ing the data out.
    "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
    May 2004
    Posts
    7
    Hi

    Basically your problem is
    Currently simply extracting the 5million nText data to file is taking 10 hours with SSIS & I only have a 2 Hour window
    Then why not DTS or SSIS to another server, say DEV box, only for the 5M unnecessary data, I mean into the new table in DEV. I do not think it would take more than 2 hours to copy 5M data into a table.

    Then do the SSIS or DTS in DEV box. I don't believe you have time limit in DEV box.

    Once you validate that all the data are in the file, then remove them in PROD box.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The problem is not in WHERE to offload the old data, but rather HOW. And the secondary question (which is really the primary goal), - HOW to get rid of it in the original table. If extracting currently takes 10 hours, then a straight DELETE would not only take longer, but will also trash the log device. So deletion process either has to be in a loop of 10K or so, or a partitioning approach will need to be used (with the ALTER TABLE...SWITCH PARTITION).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Thanks for the responses Chaps. Apologies for not responding earlier, I only got the Broadband installed in the New Aptment yesterday

    Guess we're up the creak a little with this one.

    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

Posting Permissions

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