Query to Check the Percent Complete of a Backup Query Execution (From Azure)

by

Monitoring the progress of a database backup or restore operation in Azure SQL Server is crucial for effective database management. Knowing how far along the operation is allows administrators to estimate completion times and plan their tasks accordingly. Below, we discuss a T-SQL query that provides real-time insights into the progress of backup or restore operations.

The Query:

SELECT
    session_id AS SPID,
    wait_type,
    command,
    a.text AS Query,
    start_time,
    percent_complete,
    DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) a
WHERE
    r.command IN ('RESTORE DATABASE');

Explanation of the Query:

  1. sys.dm_exec_requests: This system dynamic management view (DMV) provides details about requests currently running on the SQL Server instance.
  2. sys.dm_exec_sql_text: By cross-applying this function with the sql_handle from sys.dm_exec_requests, we can retrieve the actual text of the query being executed.
  3. Columns Returned:
    • session_id (SPID): The session ID of the query.
    • wait_type: The type of wait state the query is currently experiencing.
    • command: The type of command being executed (e.g., RESTORE DATABASE).
    • Query: The T-SQL statement text.
    • start_time: The timestamp when the query began execution.
    • percent_complete: Indicates the percentage completion of the operation.
    • estimated_completion_time: Provides an estimated completion time based on the current progress. This is calculated using the DATEADD function.
  4. WHERE r.command IN ('RESTORE DATABASE'): Filters the results to include only restore database commands. Modify this filter if you want to track backup operations (e.g., use 'BACKUP DATABASE' instead).

Key Benefits of Using This Query:

  • Real-time Monitoring: Provides instant updates on the progress of a restore operation.
  • Estimated Completion Time: Helps in scheduling and resource planning by predicting when the operation will end.
  • Comprehensive Details: The additional information, such as wait types and session IDs, can assist in troubleshooting performance issues.

Practical Use Case:

Imagine an administrator initiating a large database restore operation during off-peak hours. By running this query periodically, they can monitor the restore’s progress and ensure that it completes before peak business hours, avoiding disruptions.

Conclusion:

The provided T-SQL query is a powerful tool for monitoring backup and restore operations in Azure SQL Server. It’s simple to use and provides valuable insights that can enhance database management efficiency. Bookmark this query for quick access during database maintenance tasks.

Do you use similar queries to manage your database operations? Share your tips and tricks in the comments below!

Leave a Comment