Post reply

Can is stop DBCC CLEANTABLE

  • June 3, 2016 at 2:28 pm

    #327342

    I am in the middle of claiming space from a table using DBCC CLEANTABLE (after dropping a variable length column from it ) . Its been running for more than 8 hrs. It has been claiming space but at a snails pace.

    What is the impact of stopping the DBCC CLEANTABLE ? (i want to try it with a smaller row size and see if the pace picks up).

    Appreciate your comments

  • June 3, 2016 at 8:54 pm

    #1883238

    jude.pieries (6/3/2016)

    I am in the middle of claiming space from a table using DBCC CLEANTABLE (after dropping a variable length column from it ) . Its been running for more than 8 hrs. It has been claiming space but at a snails pace.

    What is the impact of stopping the DBCC CLEANTABLE ? (i want to try it with a smaller row size and see if the pace picks up).

    Appreciate your comments

    What batch size did you use? If you didn't include a batch size or used a batch size of "0", then the whole thing will be done in a single transaction. That means excessive log file growth, which may be the thing slowing everything down.

    And, if you didn't include a batch size or used a batch size of "0" and you stop it, then the whole thing will need to rollback, which could take longer than what you've already run.

    [font="Arial Black"]From Books Online:[/font]

    DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction.

    This operation is fully logged.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 3, 2016 at 11:21 pm

    #1883246

    HI

    used a batch is of 10000 ,

    So if i understand it correctly, if i stop the script it would rollback up to a max of 10000 ?

  • June 4, 2016 at 11:26 am

    #1883260

    jude.pieries (6/3/2016)

    HI

    used a batch is of 10000 ,

    So if i understand it correctly, if i stop the script it would rollback up to a max of 10000 ?

    Correct.

    I guess I wouldn't use less than that and might actually try a larger batch size. My next step would be to try 200,000 and see what happens. I'd also make sure that my log file was properly setup for growth and being backed up, say, one every 5 minutes to keep it from going nuts.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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