Claus on Code

A data dudes random musings about code, bugs, products, life etc.

Monitor the process of long running SQL queries like DBCC SHRINKDATABASE

When a query runs a little longer than I would expect, I sometimes starts to get nervous. Especially in the cases, where there is no status telling me when it will finish; Like for instance when I run a shrink database cmd.

You can get information about the queries running like this.


SELECT 
T.text, 
R.Status, 
R.Command, 
DatabaseName = db_name(R.database_id) , 
R.cpu_time, 
R.total_elapsed_time, 
R.percent_complete
FROM sys.dm_exec_requests R 
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T

Especially the percent_complete is nice.

Posted by:

Claus

4 responses to “Monitor the process of long running SQL queries like DBCC SHRINKDATABASE”

  1. Henrik Staun Poulsen Avatar
    Henrik Staun Poulsen

    hi Claus,

    Did you ever see anything other than Zero in the percent_complete column?
    Backup Database does.
    Maybe Create Index (with online=on)

    But mostly zero.

    Best regards,
    Henrik

  2. Claus Avatar
    Claus

    I have actually mostly used it, when I shrink a DB. Then it works ok, but it is not completely linear. But it gives me some clue about when the query will finish.

  3. Henrik Staun Poulsen Avatar
    Henrik Staun Poulsen

    hi Claus,

    I just use this, which I got from Michael B;

    SELECT top 10000 resource_description AS resource_description__, * FROM sys.dm_os_waiting_tasks
    WHERE session_id=246

    It is not perfect, it only works on multi-threaded plan. But better than nothing,

    Best regards
    Henrik

  4. Claus Avatar
    Claus

    Hi Henrik,

    Ok. Thanks for sharing 🙂

    I’ll try it the next time I’m waiting for a query.

Leave a Reply

Your email address will not be published. Required fields are marked *