MS SQL Server - fast kill/rollback

Author

fast kill/rollback

Andre

2005-12-30, 1:23 pm

I have just killed a transaction and it says it's going to take about 20
hours to roll the change back. Is there such a thing as a "fast kill"? I
don't care about rolling this back - I just want it to stop. The process is
blocking my users and I need this process killed sooner than 20 hours from
now. I also don't understand how/why a process that had run for 9 hours
could take 20 hours to rollback. It doesn't really make sense to me.

Thanks in advance.

Andre

Mike Epprecht $SQL MVP$

2005-12-30, 1:23 pm

Hi

Generally, if a transaction took 9 hours, you can expect a rollback to take
12-24 hours (1.5-3 times the forward time).

It has to read the original row from the transaction log, and apply it back
to the database. This is very IO intensive. I hope your transaction log is
on a separate physical drive/LUN.

It does not help to stop the SQL Server service as on startup, it will do
the rollback and it will take the same time.

You should never have very long transactions, as it can take a long time to
rollback as anything can happen over such a long period (power, network
server problems)

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Andre" <no@spam.com> wrote in message
news:uinG%23kWDGHA.272@TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
>
> Thanks in advance.
>
> Andre
>

Dan Guzman

2005-12-30, 1:23 pm

> Is there such a thing as a "fast kill"? I don't care about rolling this
> back - I just want it to stop.

The only way to avoid the rollback is to restore from backup.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"Andre" <no@spam.com> wrote in message
news:uinG%23kWDGHA.272@TK2MSFTNGP10.phx.gbl...
>I have just killed a transaction and it says it's going to take about 20
>hours to roll the change back. Is there such a thing as a "fast kill"? I
>don't care about rolling this back - I just want it to stop. The process
>is blocking my users and I need this process killed sooner than 20 hours
>from now. I also don't understand how/why a process that had run for 9
>hours could take 20 hours to rollback. It doesn't really make sense to me.
>
> Thanks in advance.
>
> Andre
>

Andre

2005-12-30, 8:23 pm

bummer.

In that case, maybe I should ask for advice on how to do this. I'm trying
to add a new column to a table that has 9 cols and 34 million records. I'm
trying to add an identity col called RowID, using alter table. Is there a
better way to do this? Should I drop all indexes before running this
command?

Thanks, Andre

Andrew J. Kelly

2005-12-30, 8:23 pm

Even on the worst hardware I can think of this should not take 9 hours to
do. Most likely you were blocked and it wasn't doing anything. But in any
case you might want to BCP out the data, create a new table just the way you
need with no indexes. BCP the data back in (you will probably need a format
file since the structure will be different) and recreate the indexes. That
way you can get a minimally logged load if you do the BCP or Bulk Insert
properly.

--
Andrew J. Kelly SQL MVP

"Andre" <no@spam.com> wrote in message
news:%23$vQBzXDGHA.2036@TK2MSFTNGP14.phx.gbl...
> bummer.
>
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
>
> Thanks, Andre
>

Dan Guzman

2005-12-30, 8:23 pm

Another method besides the BCP method Andrew suggested is to create a new
table using SELECT INTO, creating the new column using the IDENTITY
function. This will also be minimally logged in the SIMPLE recovery model.
You can then drop the old table and recreate constraints and indexes.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"Andre" <no@spam.com> wrote in message
news:%23$vQBzXDGHA.2036@TK2MSFTNGP14.phx.gbl...
> bummer.
>
> In that case, maybe I should ask for advice on how to do this. I'm trying
> to add a new column to a table that has 9 cols and 34 million records.
> I'm trying to add an identity col called RowID, using alter table. Is
> there a better way to do this? Should I drop all indexes before running
> this command?
>
> Thanks, Andre
>

Andre

2005-12-30, 8:23 pm

I thought about that one too but wondered about the logging. I'll give that
a try tonight and see how it goes.

I also do think there was some blocking going on. I'm going to stop SQL
Agent tonight before starting.

Thanks again,

Andre

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:OO%23OJIYDGHA.3992@TK2MSFTNGP12.phx.gbl...
> Another method besides the BCP method Andrew suggested is to create a new
> table using SELECT INTO, creating the new column using the IDENTITY
> function. This will also be minimally logged in the SIMPLE recovery
> model. You can then drop the old table and recreate constraints and
> indexes.
>
> --
> Happy Holidays
>
> Dan Guzman
> SQL Server MVP
>
> "Andre" <no@spam.com> wrote in message
> news:%23$vQBzXDGHA.2036@TK2MSFTNGP14.phx.gbl...
>
>

Sponsored Links