How to cancel an SQL 2008 rebuild/reindex

Question

How to cancel an SQL 2008 rebuild/reindex

mastersql

SSC Eights!

Points: 815
More actions
December 5, 2015 at 5:58 am

#302778

Hi
Using the SQL Server 2008 Maintenance Plan > Rebuild Index, I've scheduled this to run on Sunday at 5:30am against all the databases on server. I'm just concerned that when I go in on Monday that it might still be running :unsure:.
If that's the case, can anyone advise how it could be cancelled or check how far it need to go before finishing. As it's a live environment, I will need to restore from the backup if it's not finished in time for Monday 8am.
Hardware information: the server is a new Windows 2008 32 bit with a maximum ram installed 4GB. I don't have any more details to hand on the database size etc at the minute as I'm not at work.
Running the rebuild is the equivalent of running this script:
Kind Regards
James

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

KILL <session ID>

However do that and it will roll back, and roll back usually takes longer than the operation had up to that point. So kill an index rebuild that's been running 2 days and you'll have a very unpleasant 3 days.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mastersql SSC Eights! Points: 815 More actions · Answer 2

Hi Gila

If its still slow on Monday I'll advise that the customers have to put up with it as it'll be better in the long run.

Cheers

James

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

mastersql (12/5/2015)
Hardware information: the server is a new Windows 2008 32 bit with a maximum ram installed 4GB

Just on this point.

My 2-year old laptop has more memory than that, you're 3 versions of the OS behind and using memory addressing that was replaced over 10 years ago (32 bit). If that's a new server, there's a tad of a problem there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mastersql SSC Eights! Points: 815 More actions · Answer 4

Hi SSC

My 5 year old laptop has twice as much memory compared to this ---- lol.

I'll drop a post to say how this peters out.... Just need to prove that memory is getting low when the system jams 🙂

Cheers

Matt

kreuzer Ten Centuries Points: 1246 More actions · Answer 5

Hi,

maybe this could help, to decide to cancel the maintenance or to wait until it is finished:

SELECT a.database_id,

a.name,

b.command,

b.percent_complete,

b.start_time,

b.wait_type,

b.last_wait_type,

b.status

FROM sys.databases a

JOIN sys.dm_exec_requests b ON a.database_id = b.database_id

WHERE b.command like '%index%';

If you run this script, you are able to see the progress of the rebuild maintenance in " % ".

Best regards,

Andreas

mastersql SSC Eights! Points: 815 More actions · Answer 6

Hi kreuzer

Thanks very much - I'm about to run another rebuild in 30 minutes times so this could come in very handy!!

Also when I ran a rebuild before, it only took 10 minutes!!! I couldn't believe it - all that worry for nothing. Hopefully the rebuild scheduled in 30 minutes will go smoothly as well 🙂

Cheers

James

mastersql SSC Eights! Points: 815 More actions · Answer 7

Hi

I've ran your script but it just returns 0 rows though while the index is still running.

Cheers

Matt

kreuzer Ten Centuries Points: 1246 More actions · Answer 8

Hi,

just remove the WHERE condition, and watch for your running process. There should something with rebuild, index or?

Or just watch all running process with your SID.

Kind regards,

Andreas

mastersql SSC Eights! Points: 815 More actions · Answer 9

Hi

That's better, ta.

The percentage complete column is really useful.

It only took 15 minutes to reply rebuild a 65GB database as well so all good 😀

Cheers

James

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

mastersql (12/8/2015)
The percentage complete column is really useful.

Keep in mind that it's an estimate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 11

Just take note that rollbacks are single threaded.

So an index rebuild may take 1 hour using parallelism, but if you do a rollback, at minute 50, you may be in for a world of hurt cos it has to read the log for rollback,which is a single threaded process, and the write back to the data file will also be single threaded cos it can only happen as fast as the read on the log.

At least that is my understanding of why rollbacks can be catastrophic.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]