One of the most
important things to any DBA is monitoring the major activities progress that
occur on SQL Server instance like backup, restore, shrink data & log files.
This simply can be done
by the following script:
estimated_completion_time,CURRENT_TIMESTAMP)
Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like ''
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like ''
This script simply get
the following information:
- SQL Server instance
- SQL session id for the process running
- Percentage of the process (%)
- Estimated finish time for the current running process (calculated based on the current progress)
- Total elapsed time for the current process
- DB name for the current process
- The command that being executed by the current running process
all this information is
extracted from the dynamic management view sys.dm_exec_requests which returns information
about each request that is executing within SQL Server, also from the dynamic
management function sys.dm_exec_sql_text() which returns the text of the
SQL batch.
The following is example for the script for monitoring the
backup & restore process (it is based only on the like value in the where
clause.
Backup\Restore Process:
SELECT @@servername as 'sql_instance',
session_id,percent_complete,DATEADD(MILLISECOND,
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'Backup%'
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'Backup%'
SELECT @@servername as 'sql_instance',
session_id,percent_complete,DATEADD(MILLISECOND,
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'Restore%'
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'Restore%'
Shrinking Process:
SELECT @@servername as 'sql_instance', session_id,percent_complete,DATEADD(MILLISECOND,
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'DBCC SHRINK%'
estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time,
(total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS ,
DB_NAME(Database_id) Database_Name ,command,sqltext.TEXT
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE command like 'DBCC SHRINK%'
0 comments:
Post a Comment