It is easy to check the percentage of completion of backup or restore operations on SQL 2005
or above versions where as in the older versions we’ve to use STATS[=Percentage]
to check. In the newer versions MS introduced DMVs to monitor the progress of a
task as well as numerous other DMVs to check various SQL activities. Here
sys.dm_exec_requests can be used to check the percentage completion as
bellow.
SELECT
percent_complete, start_time,
DATEADD(ms,estimated_completion_time,GETDATE()) As EstimatedCompletionTime
,DATEDIFF(hh,GETDATE(),DATEADD(ms,estimated_completion_time,GETDATE())) HoursToGo
,datediff(hh,Start_time,GETDATE())
TotalHoursCompleted
,command, b.name
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id where session_id=88
The same statement can be used to check the below tasks as
well.
1. DBCC
CHECKDB
2. DBCC CHECKTABLE
3. DBCC
SHRINKDB
4. DBCC SHRINKFILES
5. DBCC
INDEXDEFRAG
6. ALTER INDEX
REORGANIZE
7. ROLLBACK
No comments:
Post a Comment