/*------------------------------------------------------------------------------------------------------------

03/13/2009

This script can be used to KILL MSSEARCH SQL processes that have been hung for more than 60 minues
It uses a cursor, SYSPROCESSES system table and the KILL command to achieve the result


------------------------------------------------------------------------------------------------------------*/


--declaration

DECLARE @MSSEARCH_SPID VARCHAR(5)
DECLARE @STMT VARCHAR(500)

--use a cursor to loop through the sysprocesses table

DECLARE MSS_CURSOR CURSOR FOR
	SELECT SPID
	FROM MASTER.DBO.SYSPROCESSES
	WHERE LASTWAITTYPE = 'MSSEARCH' 
	AND DATEDIFF(MINUTE,LOGIN_TIME,LAST_BATCH) > 60
	
--open a cursor
	
OPEN MSS_CURSOR
FETCH NEXT FROM MSS_CURSOR INTO @MSSEARCH_SPID

--use a while loop to fetch records from the cursor

WHILE @@FETCH_STATUS=0
	BEGIN
	
	--use the SQL KILL command
	
	SET @STMT='KILL ' + @MSSEARCH_SPID
		PRINT @STMT
		EXEC(@STMT)
		
	FETCH NEXT FROM MSS_CURSOR INTO @MSSEARCH_SPID
	END

--close the cursor

CLOSE MSS_CURSOR
DEALLOCATE MSS_CURSOR



/*

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.


*/