Post reply

sys.dm_exec_request showing negative total_elapsed_time

  • June 1, 2011 at 12:35 pm

    #252069

    Hi all,

    One of my SQL Session is opened from long time and it is a SQL Job which opened this session. This job is scheduled run for every one minute.

    I am executing the below query to see the current sessions percentage of completion, estimated completion time & total_elapsed_time.

    Query:

    select R.session_id, R.blocking_session_id, T.text, r.status, DB = db_name(R.database_id),

    R.total_elapsed_time, R.percent_complete, R.estimated_completion_time

    from sys.dm_exec_requests R cross apply sys.dm_exec_sql_text(R.sql_handle) T

    Problem: : The issue im facing is the query output showing negative "total_elapsed_time" (-547334562)

    Can any one please tell me what meant by this negative time... what exactly it is showing.

    Thanks in advance.

  • June 1, 2011 at 12:42 pm

    #1332707

    dbcc timewarp? (inside joke)

    actually, i think the issue is the times are cumulative, and are since the server restarted...

    so it's quite possible if your server is active, or has been online a long time, an integer value hit it's max value and rolled over....as other values were added to it:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 1, 2011 at 1:58 pm

    #1332768

    Thanks for the reply. According to your reply if i am not wrong, all my sessions need to show in negative times but it is not.

    Only one session showing negative completion time.

    Some sessions are opened even before this, they are showing proper timing.

  • June 1, 2011 at 2:14 pm

    #1332787

    mohan.pariveda 18256 (6/1/2011)

    Thanks for the reply. According to your reply if i am not wrong, all my sessions need to show in negative times but it is not.

    Only one session showing negative completion time.

    Some sessions are opened even before this, they are showing proper timing.

    no not all sessions would need to roll over into negatives..if you have one incredibly active ssions, it might rollover, while the other sessions don't; older, less active ssions might have very low numbers, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply