Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Unhappy Unanswered: Rollback process Taking Over 8 hours

    All,
    I started a bcp process to bcp in 25 million rows and the log filled up.
    The process was killed, however it is taking hours upon hours to rollback.
    Can I issue a dump tran <dbname> with no_log while the rollback is still running? I had to add more space for the log, and now it has filled up again.
    I don't want to keep having to add space. The amount of space allocated to log already is 38 gig.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    It looks like you did a "slow" bcp and didn't include a batch size (i.e. no commit at intervals during the load).

    The amount of time needed for the rollback is essentially the same as the time needed to fill up the log in the first place.

    Killing the process, or stopping/starting ASE will not help, as all of the pages that were initially allocated now have to be deallocated.

    Michael

  3. #3
    Join Date
    Jan 2006
    Posts
    6

    Question

    Thanks for the reply Michael.

    Yes, that was my error. I usually issue the comand with -b100000, but neglected to set that option in the script I created.

    To dump the database takes about 2.5 hours. We usually dump this database only on the weekend, so could I just dump the database to reclaim space for the log even though the rollback is still executing?
    Last edited by DBAFreeAgent; 01-26-06 at 18:00.

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I don't think so - a db dump doesn't truncate the log in any case, and I don't think that the log can be truncated until the rollback has completed.

    Michael

  5. #5
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    There is a "get out of the jail" card called Suicide the Log. This will completely kill the transaction log and start clean. However, this step comes with it's own vices and sometimes may leave a database corrupt and data integrity issues.

    Suicide the Log used to be documented step in the Error Messages Guide until 12.0.x and those steps still work on later versions. The disclaimers apply too, sort of "do it at your own risk".

  6. #6
    Join Date
    Mar 2006
    Posts
    25

    Unhappy

    Now u cannot issue dump tran with no_log because rollback is going on
    I think u should have aborted all the transactions in particular database
    using

    in target database
    select lct_admin(abort,0,db_id)

    Quote Originally Posted by DBAFreeAgent
    All,
    I started a bcp process to bcp in 25 million rows and the log filled up.
    The process was killed, however it is taking hours upon hours to rollback.
    Can I issue a dump tran <dbname> with no_log while the rollback is still running? I had to add more space for the log, and now it has filled up again.
    I don't want to keep having to add space. The amount of space allocated to log already is 38 gig.

Posting Permissions

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