none
Index Rebuild in SQL 2005 RRS feed

  • Question

  • I am doing a single pk index rebuild on a 100 gig table.  After running sp_who2, I see that the rebuild in suspended state.  Over a period of time, I have noticed that neither disk io nor cpu io is changing.   Is there a way for me to cancel it?  Is it frozen forever?

    Any help is appreciated 

    Wednesday, October 13, 2010 4:21 PM

Answers

  • Try to find out what session is blocking the process with this script and possibly kill the session.

    /*
    Shows blocked and blocking processes. Even if it works across all database, ObjectName 
    populates for current database only. Could be modified with dynamic SQL if needed
    
    Be careful with Query text for BLOCKING session. This represents currently active
    request for this specific session id which could be different than query which produced locks
    It also could be NULL if there are no active requests for this session
    */
    select
    	TL1.resource_type
    	,DB_NAME(TL1.resource_database_id) as [DB Name]
    	,CASE TL1.resource_type
    		WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
    		WHEN 'DATABASE' THEN 'DB'
    		ELSE
    			CASE 
    				WHEN TL1.resource_database_id = DB_ID() 
    				THEN
    					(
    						select OBJECT_NAME(object_id, TL1.resource_database_id)
    						from sys.partitions
    						where hobt_id = TL1.resource_associated_entity_id
    					)
    				ELSE
    					'(Run under DB context)'
    			END
    	END as ObjectName
    	,TL1.resource_description
    	,TL1.request_session_id
    	,TL1.request_mode
    	,TL1.request_status
    	,WT.wait_duration_ms as [Wait Duration (ms)]
    	,(
    		select
    			SUBSTRING(
    				S.Text, 
    				(ER.statement_start_offset / 2) + 1,
    				((
    					CASE 
    						ER.statement_end_offset
    					WHEN -1 
    						THEN DATALENGTH(S.text)
    						ELSE ER.statement_end_offset
    					END - ER.statement_start_offset) / 2) + 1)		
    		from 
    			sys.dm_exec_requests ER 
    				cross apply sys.dm_exec_sql_text(ER.sql_handle) S
    		where
    			TL1.request_session_id = ER.session_id
    	 ) as [Query]
    from
    	sys.dm_tran_locks as TL1 join sys.dm_tran_locks TL2 on
    		TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
    	left outer join sys.dm_os_waiting_tasks WT on
    		TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
    
    where
    	TL1.request_status <> TL2.request_status and
    	(
    		TL1.resource_description = TL2.resource_description OR
    		(TL1.resource_description is null and TL2.resource_description is null)
    	)
    go
    
    


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NModerator Thursday, October 14, 2010 3:15 AM
    • Marked as answer by rgelfand Thursday, October 14, 2010 4:40 PM
    Wednesday, October 13, 2010 4:42 PM

All replies

  • Try to find out what session is blocking the process with this script and possibly kill the session.

    /*
    Shows blocked and blocking processes. Even if it works across all database, ObjectName 
    populates for current database only. Could be modified with dynamic SQL if needed
    
    Be careful with Query text for BLOCKING session. This represents currently active
    request for this specific session id which could be different than query which produced locks
    It also could be NULL if there are no active requests for this session
    */
    select
    	TL1.resource_type
    	,DB_NAME(TL1.resource_database_id) as [DB Name]
    	,CASE TL1.resource_type
    		WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
    		WHEN 'DATABASE' THEN 'DB'
    		ELSE
    			CASE 
    				WHEN TL1.resource_database_id = DB_ID() 
    				THEN
    					(
    						select OBJECT_NAME(object_id, TL1.resource_database_id)
    						from sys.partitions
    						where hobt_id = TL1.resource_associated_entity_id
    					)
    				ELSE
    					'(Run under DB context)'
    			END
    	END as ObjectName
    	,TL1.resource_description
    	,TL1.request_session_id
    	,TL1.request_mode
    	,TL1.request_status
    	,WT.wait_duration_ms as [Wait Duration (ms)]
    	,(
    		select
    			SUBSTRING(
    				S.Text, 
    				(ER.statement_start_offset / 2) + 1,
    				((
    					CASE 
    						ER.statement_end_offset
    					WHEN -1 
    						THEN DATALENGTH(S.text)
    						ELSE ER.statement_end_offset
    					END - ER.statement_start_offset) / 2) + 1)		
    		from 
    			sys.dm_exec_requests ER 
    				cross apply sys.dm_exec_sql_text(ER.sql_handle) S
    		where
    			TL1.request_session_id = ER.session_id
    	 ) as [Query]
    from
    	sys.dm_tran_locks as TL1 join sys.dm_tran_locks TL2 on
    		TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
    	left outer join sys.dm_os_waiting_tasks WT on
    		TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
    
    where
    	TL1.request_status <> TL2.request_status and
    	(
    		TL1.resource_description = TL2.resource_description OR
    		(TL1.resource_description is null and TL2.resource_description is null)
    	)
    go
    
    


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NModerator Thursday, October 14, 2010 3:15 AM
    • Marked as answer by rgelfand Thursday, October 14, 2010 4:40 PM
    Wednesday, October 13, 2010 4:42 PM
  • Are you doing the rebuild via a SQL Agent Job?

    If you are then the SQL Agent Job will show as "Executing"; if it is safe to do so then right-click the job and stop it...


    Please click "Mark As Answer" if my post helped. Tony C.
    Wednesday, October 13, 2010 4:47 PM
  • try to see how much percent is remaining from sys.dm_exec_requests .Not sure if it will show something for it or not as I have seen that for many SPIDs it shows 0 : select percent_complete,estimated_completion_time,reads,writes from sys.dm_exec_requests where Session_id =<SPID> ..This will also show you if its still or doing something ...

    also do KILL SPID with Statusonly : This will tell you how much time will it take to rollback.


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, October 13, 2010 5:13 PM
  • Excellent script.  I have made good use of it already.
    Thursday, October 14, 2010 4:41 PM