July 10, 2019 at 2:48 pm
I poked around online and could not find anything very useful which would return basic info relating to index maintenance (% complete).
I know I can fire up profiler and see activity that way, I am curious if there is a script that exists. I have reviewed and tried a few I have found online and none really work...
Thanks in advance,
Lee
July 10, 2019 at 2:52 pm
One way to do it would be to dump the results of sp_whoisactive into a temp table and filter out statements not related to index maintenance.
John
July 10, 2019 at 3:03 pm
try this one:
SELECT r.[session_id]
,s.original_login_name
,c.[client_net_address]
,s.[host_name]
,c.[connect_time]
,r.blocking_session_id [blocking]
,s.[last_request_start_time] [request_start_time]
,CURRENT_TIMESTAMP [current_time]
,r.[percent_complete] [Done%]
,dateadd(millisecond, r.[estimated_completion_time], CURRENT_TIMESTAMP) [estimated_finish_time]
,r.status
,current_command = SUBSTRING(t.[text], r.[statement_start_offset] / 2, COALESCE(NULLIF(r.[statement_end_offset], - 1) / 2, 2147483647))
,module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_connections AS c ON r.[session_id] = c.[session_id]
INNER JOIN sys.dm_exec_sessions AS s ON r.[session_id] = s.[session_id]
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.[percent_complete] <> 0;
GO
July 10, 2019 at 4:55 pm
Hi Andrey,
That looks pretty good, it at least gave me something to go on. I appreciate it!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply