So I was running a “Shrink Database” in SQL and it was taking FOREVER to complete. So, I thought… There’s got to be a way to see the status. Sure enough, there was. I’ve put together a little script that will tell you active exec requests and provide some of the vitals about the processes. Take a look!
[code language=”sql”]
SELECT
command AS [Command],
USER_NAME(user_id) AS [User],
start_time AS [Start Time],
— Calculate SQL time into readable time
CASE WHEN ((total_elapsed_time/1000)/3600) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(total_elapsed_time/1000)/3600)
ELSE CONVERT(VARCHAR(10),(total_elapsed_time/1000)/3600)
END + ‘:’ +
CASE WHEN ((total_elapsed_time/1000)%3600/60) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(total_elapsed_time/1000)%3600/60)
ELSE CONVERT(VARCHAR(10),(total_elapsed_time/1000)%3600/60)
END + ‘:’ +
CASE WHEN ((total_elapsed_time/1000)%60) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(total_elapsed_time/1000)%60)
ELSE CONVERT(VARCHAR(10),(total_elapsed_time/1000)%60)
END
AS [Time Elapsed],
— Calculate SQL time into readable time
CASE WHEN ((estimated_completion_time/1000)/3600) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(estimated_completion_time/1000)/3600)
ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)/3600)
END + ‘:’ +
CASE WHEN ((estimated_completion_time/1000)%3600/60) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(estimated_completion_time/1000)%3600/60)
ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)%3600/60)
END + ‘:’ +
CASE WHEN ((estimated_completion_time/1000)%60) < 10 THEN ‘0’ +
CONVERT(VARCHAR(10),(estimated_completion_time/1000)%60)
ELSE CONVERT(VARCHAR(10),(estimated_completion_time/1000)%60)
END
AS [Time Remaining],
percent_complete AS [% Completed],
session_id AS [Session ID],
status AS [Status]
FROM
sys.dm_exec_requests
WHERE
percent_complete > 0
[/code]