Killing a DBCC DBREINDEX - will this cause a massive rollback

I recall several instances where I killed a DBREINDEX after 20 minutes of
execution and it seemed to cancel surprisingly fast (I was expecting a
massive rollback)
We now have a situation where a DBREINDEX has been running over 12 hours.
If I were to kill this - could it be assumed that it would just become
available immediately or is there a chance of a BIG rollback (which could
possibly take another 12 hours)?
Thanks in advanceIt depends when you cancel it and what you've asked it to do.
If you have a dbreindex of a single very large index, and cancel it while
its still building the new index, it should cancel pretty fast as all it
does is deallocate all the new pages. If you cancel it while it's deleting
the old index, then it has to rollback all the deletes again.
If you have a dbreindex that's rebuilding 10 indexes, and you cancel it
after its done 5 of them, it will have to rollback the deletes of the 5 old
indexes, which will take time proportional to their size.
Several things to consider: why are you rebuilding and have you considered
using DBCC INDEXDEFRAG instead?
Checkout the whitepaper below for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23Hi2ZwjuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I recall several instances where I killed a DBREINDEX after 20 minutes of
> execution and it seemed to cancel surprisingly fast (I was expecting a
> massive rollback)
> We now have a situation where a DBREINDEX has been running over 12 hours.
> If I were to kill this - could it be assumed that it would just become
> available immediately or is there a chance of a BIG rollback (which could
> possibly take another 12 hours)?
> Thanks in advance
>

Key cannot be null. Parameter name: key

We just upgraded to SS2005. After the upgrade, we can no longer create
subscriptions out of Execution Snapshot.
Try this experiment:
Create a report, with a simple SQL statement like "select * from CustomerDim
where CustomerPk = @.CustomerPk"
Go to Report Parameter, check the "Allow Null" box
Deploy the report
Go to the report on report server, render the report from Execution Snapshot
Now go to Subscription, and try to create a new subscription. The Parameter
CustomerPk will be grayed out, because of the Execution Snapshot
After you clicked OK, the subscription will return the error:
Key cannot be null. Parameter name: key
This error does not occur before the upgrade.
How can we create subscriptions out of Execution Snapshot in SS2005?
Thanks,
John WI've got the same problem. Did you find any solution?
Regards,
/Mattias
"LBJOHN" wrote:
> We just upgraded to SS2005. After the upgrade, we can no longer create
> subscriptions out of Execution Snapshot.
> Try this experiment:
> Create a report, with a simple SQL statement like "select * from CustomerDim
> where CustomerPk = @.CustomerPk"
> Go to Report Parameter, check the "Allow Null" box
> Deploy the report
> Go to the report on report server, render the report from Execution Snapshot
> Now go to Subscription, and try to create a new subscription. The Parameter
> CustomerPk will be grayed out, because of the Execution Snapshot
> After you clicked OK, the subscription will return the error:
> Key cannot be null. Parameter name: key
> This error does not occur before the upgrade.
> How can we create subscriptions out of Execution Snapshot in SS2005?
> Thanks,
> John W