How can I cancel index creation in SQL Server

How can I cancel an index creation?

It is still running and will run for a long time, because by accident I have created a clustered index; and non clustered indices are recreating on a really huge table.

One way is to find the process/session Id of the query and kill it. You can learn more here. This might be about the only way if you don't know who or what actually started the index creation.

The following query should show you the session Id of the Insert query:

SELECT 
    r.session_id as SessionID,
    r.start_time as StartTime,
    r.[status] as Status, 
    r.wait_type as WaitType, 
    r.blocking_session_id as BlockedBySessionID,
    sessions.login_name as BlockedByUser,
    SUBSTRING(qt.[text],r.statement_start_offset / 2, 
        (CASE 
            WHEN r.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 
            ELSE r.statement_end_offset 
            END - r.statement_start_offset) / 2) AS SQLStatement,
    DB_NAME(qt.[dbid]) AS DatabaseName, 
    r.cpu_time as CPUTime, 
    r.total_elapsed_time as TotalElapsedTime, 
    Round(r.total_elapsed_time / 1000.0 / 60.0,1) as TotalElapsedTimeInMinutes,
    r.reads as Reads, 
    r.writes as Write, 
    r.logical_reads as LogicalReads
FROM sys.dm_exec_requests AS r 
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    LEFT OUTER JOIN sys.dm_exec_sessions sessions ON sessions.session_id = r.blocking_session_id
WHERE r.session_id > 50 -- This eliminates system requests
ORDER BY r.start_time

StackExchange Database Administrators Q#26958, answer score: 7