How can you identify a process that has been cancelled?
for example:
I was running this dynamic sql - via a linked server:
declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf CHAR(2); SELECT @vCrlf = CHAR(13)+CHAR(10) ,@Z = COUNT(*) ,@I = 1 FROM #T1 SELECT @sql = ' SET IDENTITY_INSERT bocss2.DBO.tblBCataloguePriceSetItem ON; INSERT INTO bocss2.dbo.tblBCataloguePriceSetItem( [lngCataloguePriceSetItemID] ,[lngCataloguePriceSetID] ,[strItemNo] ,[strTier1] ,[strTier2] ,[strTier3] ,[strTier4] ,[strTier5] ,[strTier6] ,[sintNameStructureID] ,[strCurrencyCode] ,[decPrice] ,[decWasPrice] ,[strBCCurrencyCode] ,[decBCPrice] ,[decBCWasPrice]) SELECT [lngCataloguePriceSetItemID] ,[lngCataloguePriceSetID] ,[strItemNo] ,[strTier1] ,[strTier2] ,[strTier3] ,[strTier4] ,[strTier5] ,[strTier6] ,[sintNameStructureID] ,[strCurrencyCode] ,[decPrice] ,[decWasPrice] ,[strBCCurrencyCode] ,[decBCPrice] ,[decBCWasPrice] FROM TABLEBACKUPS.DBO._mm_20140324_tblBCataloguePriceSetItem ORDER BY [lngCataloguePriceSetItemID]' BEGIN TRY --PRINT (@SQL) EXECUTE [SQLBOCSSLON1.DEV.BODEN.LOCAL].[bocss2].[dbo].[sp_executesql] @SQL END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH via a LINKED server, [SQLBOCSSLON1.DEV.BODEN.LOCAL]
when I realised the OLEDB WAIT_TYPE going to the roof, so I cancelled this query, copied and paste the script in the REAL server and run it from there.
This is the OLEDB WAIT TYPE: 
This is the picture of cancelling the query via linked server:
These are the 2 processes that are currently running on the server. In yellow is the one that I have cancelled. Wait_type is SOS_SCHEDULER_YIELD, but I cannot see any indication (at least not in my query) - that shows me that this is a process that has been cancelled.
How can I achieve that?
The exact query that I am using is here:
how to find the T-SQL of a sleeping session that is blocking some other process?
and here:
how to get the information about sleeping processes that are causing blocking?
