March 15, 2014 at 5:52 am
Hi
What happen when I stop rebuilding a clustered index on a 400 G data table?
I stopped it and the rollback operation has taken 3 hours and has not finished yet.
What can I do ,the whole database is locked.
March 15, 2014 at 6:42 am
You just have to wait for the rollback to complete (and yes it may take a very long time for such a large table). What do you mean the entire database is locked? The locks should just be on the table itself (unless you are clicking around in SSMS, then you typically will get locked up - but you should be free to run TSQL in another instance of SSMS
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2014 at 6:49 am
MyDoggieJessie (3/15/2014)
What do you mean the entire database is locked? The locks should just be on the table itself (unless you are clicking around in SSMS, then you typically will get locked up - but you should be free to run TSQL in another instance of SSMS
Yes when I want to click around in SSMS to open the database I get the locked error ,
and another question is that does it need free space for log or tempdb in rollback?
March 15, 2014 at 6:57 am
you will need to run a query to find "your" blocked spid and kill that if you want to use the current SSMS application you have open (you will need to do this several times to unblock it).
It depends. Did you specify it to sort in tempdb on or off?
Yes it is logged. If you are in full recovery make sure your transaction log backups are running regularly
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2014 at 7:06 am
how long does it take to finish approximately?
No the sort in tempdb option is off
you mean by this script I find the blocked spid and kill it manually?
SELECT p.spid,
DB_NAME(p.dbid) AS DBName,
p.hostname,
p.loginame,
p.blocked AS blocked_by,
(
SELECT t.[text]
FROM sys.dm_exec_sql_text(sql_handle) AS t
) AS [QUERY],
p.waittime,
p.cpu,
p.[status],
p.program_name,
p.cmd,
p.waittype,
p.nt_username,
p.login_time,
p.nt_domain,p.memusage
FROM sys.sysprocesses p
WHERE p.spid > 50
AND DB_NAME(p.dbid)=dbname
I checked in activity monitor and rebuild statement is the only statement running on database
March 15, 2014 at 8:14 am
A rollback takes as long or longer than the operation had until the point it was cancelled. You wait, because there's nothing else you can do.
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
March 15, 2014 at 9:37 am
A rollback takes as long or longer than the operation had until the point it was cancelled. You wait, because there's nothing else you can do.
Gile,
Suppose an index rebuild job was running for 5 hours. In case if u cancel it then, will it take a minimum 5 hours or longer to rolllback ..?
March 15, 2014 at 11:07 am
Joy Smith San (3/15/2014)
A rollback takes as long or longer than the operation had until the point it was cancelled. You wait, because there's nothing else you can do.
Gile,
Suppose an index rebuild job was running for 5 hours. In case if u cancel it then, will it take a minimum 5 hours or longer to rolllback ..?
Yep.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 16, 2014 at 4:05 am
Joy Smith San (3/15/2014)
A rollback takes as long or longer than the operation had until the point it was cancelled. You wait, because there's nothing else you can do.
Gile,
Suppose an index rebuild job was running for 5 hours. In case if u cancel it then, will it take a minimum 5 hours or longer to rolllback ..?
Assuming that was 5 hours of processing and not a blocked process waiting that long, yes, 5 hours or more. But, if the process was blocked, which is what caused it to run so long, the rollback could be fast.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 16, 2014 at 9:19 am
Thanks all.....Once one of our weekly index rebuild which normaly takes 7 hours was not compeleted till Monday morning. However when I cancelled the job it took hardly an hour to rollback. Hence I asked... Yes, there was blocking of course...
March 16, 2014 at 2:33 pm
Joy Smith San (3/16/2014)
Thanks all.....Once one of our weekly index rebuild which normaly takes 7 hours was not compeleted till Monday morning. However when I cancelled the job it took hardly an hour to rollback. Hence I asked... Yes, there was blocking of course...
It might actually work out better if you determined what the other things that were blocking were doing and maybe kill their spids instead of the index rebuild spid.
Shifting gears, killing spids always scares the hell out of me especially since it can leave a spid in a "zero rollback" that does nothing but eat CPUs. Frequently, the only way to fix those is to bounce the service. There's actually a CONNECT item on this problem.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply