August 12, 2016 at 1:08 am
Hi,
I used the below query
select
CONVERT(TIME, DATEADD(ms, DATEDIFF(ms, last_request_start_time, GETDATE()), 0), 114) AS Duration,
*
from sys.dm_exec_sessions
where (status <> 'sleeping' or open_transaction_count > 0)
order by last_request_end_time
but it returns some rows with
>>>>>>>>>>>>>>>>
00:01:49.4200000
...
23:59:59.9970000
23:59:59.9700000
>>>>>>>>>>>>>>>>
It seems to me that the last two rows were negative duration, and why it would happen?
Regards,
August 12, 2016 at 4:10 am
You have everything you need to investigate further yourself.
Return getdate(), last_request_start_time, DATEDIFF(ms, last_request_start_time, GETDATE()) in your results - right next to each other.
I suppose we can theorise that sql server has not worked out how to travel backwards in time so the .997 results may be due to rounding or imprecise data type conversions.
You can prove that by breaking your query down into individual steps and seeing at what point time starts to bend 😉
August 12, 2016 at 6:55 am
What happens if you don't convert to time? Maybe you have some very long running process or transactions that were left open.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply