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
>

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 advance
It 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/pro.../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
>

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/pr...n/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
>

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?
DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?
|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?

>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>
>.
>
|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...[vbcol=seagreen]
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
> process, the
> you will end up with
> DBREINDEX, and you will
> time as DBCC
> message

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?

>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>
>.
>|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...[vbcol=seagreen]
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
>
> process, the
> you will end up with
> DBREINDEX, and you will
> time as DBCC
> messagesql

Kill DBCC Reindex?

Can anyone explain what happens if you kill a dbcc
reindex command (all indexes)?DBCC DBREINDEX is one transaction, and when you kill a process, the
transactions that it is running will be rolled back. So you will end up with
the same situation you had before you ran DBCC DBREINDEX, and you will
probably have to wait quite a long time (maybe the same time as DBCC
DBREINDEX has run already) for the rollback to complete.
--
Jacco Schalkwijk
SQL Server MVP
"Mitch" <anonymous@.discussions.microsoft.com> wrote in message
news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
> Can anyone explain what happens if you kill a dbcc
> reindex command (all indexes)?|||So if the process was blocking users to access a
database, and you killed the process, it just returns the
indexes back to their original form? Doesn't damage the
indexes?
>--Original Message--
>DBCC DBREINDEX is one transaction, and when you kill a
process, the
>transactions that it is running will be rolled back. So
you will end up with
>the same situation you had before you ran DBCC
DBREINDEX, and you will
>probably have to wait quite a long time (maybe the same
time as DBCC
>DBREINDEX has run already) for the rollback to complete.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>> Can anyone explain what happens if you kill a dbcc
>> reindex command (all indexes)?
>
>.
>|||It will return the indexes to what they were before. Your users can't access
the table though while the rollback is in progress.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:5e4a01c474c5$c87f45e0$a401280a@.phx.gbl...
> So if the process was blocking users to access a
> database, and you killed the process, it just returns the
> indexes back to their original form? Doesn't damage the
> indexes?
>>--Original Message--
>>DBCC DBREINDEX is one transaction, and when you kill a
> process, the
>>transactions that it is running will be rolled back. So
> you will end up with
>>the same situation you had before you ran DBCC
> DBREINDEX, and you will
>>probably have to wait quite a long time (maybe the same
> time as DBCC
>>DBREINDEX has run already) for the rollback to complete.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Mitch" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:5d3c01c474c1$f6c40c50$a301280a@.phx.gbl...
>> Can anyone explain what happens if you kill a dbcc
>> reindex command (all indexes)?
>>
>>.

Keys out of order on page error

We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenThere are a couple of things.
Firstly it is very intensive so turn off as many services
you can get away with, i.e SQL Agent
Secondly depending upon the severity of the error it may
result in data loss. Personally I would try firstly with
REPAIR_FAST, then REPAIR_REBUILD and if all else fails
REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
data.
If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
have to restore from backup.
As for the reason why to difficult to say.
Good luck
Peter
>--Original Message--
>We have found we have corruption in our db.
>After running DBCC CHECKDB the following error were
displayed on
>multiple pages.
>Server: Msg 2511, Level 16, State 1, Line 1
>Table error: Object ID 1285579618, Index ID 3. Keys out
of order on page
>(1:384662), slots 75 and 76.
>I read a post that suggested running DBCC CHECKDB with
the repair_build
>option.
>Since we are a 24/7 shop I cannot restore from tape,
because it would
>take several hours.
>What are the risks of running DBCC CHECKDB with the
repair_build option,
>and will this fix the problem properly?
>We are still unsure what has caused this corruption, any
body else have
>this problem?
>TIA,
>-Ben
>.
>|||Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
--
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||In addition:
The latest update of Books Online has some very detailed recommendations for most error numbers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:374a01c47eee$f6a6a890$a501280a@.phx.gbl...
> There are a couple of things.
> Firstly it is very intensive so turn off as many services
> you can get away with, i.e SQL Agent
> Secondly depending upon the severity of the error it may
> result in data loss. Personally I would try firstly with
> REPAIR_FAST, then REPAIR_REBUILD and if all else fails
> REPAIR_ALLOW_DATA_LOSS, its that option where you may lose
> data.
> If the REPAIR_ALLOW_DATA_LOSS doesn't work then you will
> have to restore from backup.
> As for the reason why to difficult to say.
> Good luck
> Peter
>
> >--Original Message--
> >We have found we have corruption in our db.
> >
> >After running DBCC CHECKDB the following error were
> displayed on
> >multiple pages.
> >
> >Server: Msg 2511, Level 16, State 1, Line 1
> >Table error: Object ID 1285579618, Index ID 3. Keys out
> of order on page
> >(1:384662), slots 75 and 76.
> >
> >I read a post that suggested running DBCC CHECKDB with
> the repair_build
> >option.
> >
> >Since we are a 24/7 shop I cannot restore from tape,
> because it would
> >take several hours.
> >
> >What are the risks of running DBCC CHECKDB with the
> repair_build option,
> >and will this fix the problem properly?
> >
> >We are still unsure what has caused this corruption, any
> body else have
> >this problem?
> >
> >TIA,
> >
> >-Ben
> >.
> >|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>

Keys out of order on page error

We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-Ben
Hi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben
|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name>). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. You
> might have hardware problems.
>

Keys out of order on page error

We have found we have corruption in our db.
After running DBCC CHECKDB the following error were displayed on
multiple pages.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
(1:384662), slots 75 and 76.
I read a post that suggested running DBCC CHECKDB with the repair_build
option.
Since we are a 24/7 shop I cannot restore from tape, because it would
take several hours.
What are the risks of running DBCC CHECKDB with the repair_build option,
and will this fix the problem properly?
We are still unsure what has caused this corruption, any body else have
this problem?
TIA,
-BenHi Ben,
As the error is in a non-clustered index, you can just rebuild the index
with DBCC DBREINDEX (<table_name>, <index_name> ). Or if you want to be on
the safe side, just drop and recreate the index.
After that, investigate why the corruption occurred in the first place. You
might have hardware problems.
Jacco Schalkwijk
SQL Server MVP
"Ben" <bmilliron@.gmail.com> wrote in message
news:OJgEzgufEHA.636@.TK2MSFTNGP12.phx.gbl...
> We have found we have corruption in our db.
> After running DBCC CHECKDB the following error were displayed on multiple
> pages.
> Server: Msg 2511, Level 16, State 1, Line 1
> Table error: Object ID 1285579618, Index ID 3. Keys out of order on page
> (1:384662), slots 75 and 76.
> I read a post that suggested running DBCC CHECKDB with the repair_build
> option.
> Since we are a 24/7 shop I cannot restore from tape, because it would take
> several hours.
> What are the risks of running DBCC CHECKDB with the repair_build option,
> and will this fix the problem properly?
> We are still unsure what has caused this corruption, any body else have
> this problem?
> TIA,
> -Ben|||I have dropped and recreated the index, thanks. I have also opened up
an issue with our vendor to see if this may be a possible cause.
Jacco Schalkwijk wrote:
> Hi Ben,
> As the error is in a non-clustered index, you can just rebuild the index
> with DBCC DBREINDEX (<table_name>, <index_name> ). Or if you want to be on
> the safe side, just drop and recreate the index.
> After that, investigate why the corruption occurred in the first place. Yo
u
> might have hardware problems.
>