Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: import data problem

    Hi All -

    I'm trying to use the "import data" option in Management Studio (sql server 2005) to get data from one server to another. It works for smaller tables, but on one of my larger tables it fills tempdb (I am not loading to tempdb!). Any idea why it would be using tempdb, and how I can get it to NOT use tempdb? At this point I think I'm going to just use bcp out/ bcp in to get the data over, but I'm sure that's not the fastest way...

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sounds like a wise plan.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are there any current snapshots of this database? When you have a snapshot of a database, the updated pages are moved to tempdb.

  4. #4
    Join Date
    Jul 2006
    Posts
    28
    Thanks for the replies - No, the database doesn't have snapshots.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about insert triggers on the table?

  6. #6
    Join Date
    Jul 2006
    Posts
    28
    No, no triggers either. table schema is the same on source & destination.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Does the target table have any indexes?
    "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
    Jul 2006
    Posts
    28
    Yes, it has one clustered and seven(!) nonclustered indexes. I'm still at a loss as to why this would use tempdb..?

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    BTree index structure update occurs in tempdb. Drop the indexes before loading the data. Besides, that's according to best practices anyway
    "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
  •