Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kdfarber
Starting Member
24 Posts |
 Posted - 2004-04-06 : 13:48:27
|
| I have a job executing that is creating a clustered index and it is blocking out users. The users are using Crystal Enterprise and it is trying to execute sp_index_rowset. Of course, it can't select against sysindexes because of the create index. This create has been executing almost three hours. If I were the cancel the job will it initiate a rollback and does it normally take the same about of time to roll back ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 13:53:19
|
| Yes it will need to roll it back. Yes it will take about the same amount of time to roll it back.I would definitely create the index at night when the system has slowed down.Tara |
 |
|
|
kdfarber
Starting Member
24 Posts |
Posted - 2004-04-06 : 13:55:46
|
| Thanks for the response. Thanks for confirming the rollback. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-07 : 10:10:22
|
| I am not sure it would take the same amount of time, would it? I mean, part of the issue with clustered indexes is that it sorts the data, I don't think if you cancelled the transaction that it would really try to UN-sort the data again, would it?In general, though, she's right, it takes as long to roll back as it did to run in the first place. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 10:36:10
|
| It doesn't really analyze the processes it's rolling back. It says transaction started at point A in transaction log and you are at point c.rollback to point brollback to point aSo, it should take nearly as long regardless of the actions performed.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-07 : 13:50:59
|
| Yeah, but a lot of the time consuming stuff is the CPU sorting the data and determining what arder it's going to be in. It doesn't have to do that when rolling the transaction back, because the transaction log doesn't say "compare x records, put this one in the sort table in this place, put this one here, etc" it just has to remove the records from the sort table. Should be a good deal quicker.In the same way that if I rollback the tran on an Insert Into...WHERE statement, it is only writing records to the trans log once it finds a match to the where clause, so if I worte a really bad where clause that was doing a bunch of tablescans, the rollback would be much quicker than the insert. |
|
|
|
|
|
|
|
|
Cluster Index build