Tracking activities on SQL Server

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:

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 ''

This script simply get the following information:

1. SQL Server instance 
2. SQL session id for the process running
3. Percentage of the process (%)
4. Estimated finish time for the current running process (calculated based on the current progress)
5. Total elapsed time for the current process
6. DB name for the current process
7. 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%'

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%'

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%'