Recently bumped into an issue where we needed to kill a large insert, obviously causing SQL Server to perform a rollback. I needed to find out what was going on, so looked to Pinal Dave’s blog post about finding out which queries are running, which you can find here. Which uses the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs.
The below script adds a few little things, to tell you for a given SPID, what the SQL text is that’s being executed and most importantly the expected finish time.
--Set how long you want the script to run for in HH:MM:SS format. --The longer you leave it running the more accurate it will be DECLARE @Delay VARCHAR(20) = '00:00:10'; --Add your SPID, which you can get from sp_who2 DECLARE @SPID INT = 206; DECLARE @StartPercent DECIMAL(18,9), @Statement VARCHAR(MAX), @EndPercent DECIMAL(18,9), @DelaySeconds INT, @PercentToGo DECIMAL(18,9), @Increase DECIMAL(18,9); --Set the starting percentage complete SELECT @StartPercent = req.percent_complete, @Statement = sqltext.TEXT FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext WHERE session_id = @SPID --Wait for the specified delay time WAITFOR DELAY @Delay --Get the end percentage complete SELECT @EndPercent = req.percent_complete FROM sys.dm_exec_requests req WHERE session_id = 206 SET @PercentToGo = 100 - @EndPercent; SET @Increase = @EndPercent - @StartPercent SET @DelaySeconds = DATEDIFF(SECOND, 0, @Delay) --Return the output including the expected finish time SELECT @SPID [SPID], @Statement [SQLText], @DelaySeconds [DelaySeconds], @StartPercent [StartPercent], @EndPercent [EndPercent], @Increase [Increase], DATEADD(SECOND,(@PercentToGo / @Increase) * @DelaySeconds, GETDATE()) [ExpectedFinishTime]
You can use the same query to monitor the below events (source):
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION