Can is stop DBCC CLEANTABLE

Question

Can is stop DBCC CLEANTABLE

jude.pieries

Default port

Points: 1438
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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)

jude.pieries Default port Points: 1438 More actions · Answer 2

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 ?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)