Post reply

DMV's=>Intersting ->sys.dm_exec_requests

  • October 25, 2010 at 9:16 am

    #225438

    I have one Question?

    I have Observed that we have run the DBCC CHECKDB on an 5TB and App's team is asking every an hour status on the DBCC CHECKDB

    How Much percentage completed and what will be the Estimation of time to complete the DBCC CHECKDB..

    For this we knew that in SQL server 2005 -we have good DMV Command

    "sys.dm_exec_requests" or

    we can use

    Use <Mydatabase>

    Go

    SELECT TOP 2 start_time,

    percent_complete ,estimated_completion_time

    FROM sys.dm_exec_requests

    ORDER by start_time desc

    But I was bit Happy yes we can use and inform them of Percentage of completion..Fine we were monitoring it. and after executing this command after an

    45Mins (Initially i have fired the query what is processes status of DBCC Checkdb-found that the SPID is in Suspended status) then I can see that

    int percent_complete and Estimated_completion_time is Zero ......I was monitoring at every 10 mins still SPID is in Suspneded and Zero estimation time and %Also

    So my question is that --I believe if the SPID is is runnable I can see both the colum output of percent_complete ,estimated_completion_time...

    Why in if SPID is reflected to suspneded status(consider query has run more the 2Hrs) shows Zero status in percent_complete ,estimated_completion_time

    what will happens if the query again reflected to Runnable state after an 2 Hrs(I have not monitored it but curios to ask) --Then How it consider the

    percent_complete ,estimated_completion_time.

    We already knew about the

    suspended. The session is waiting for an event, such as I/O to complete Also we knew that we cannot say how much time may take to completion(if we can say

    that will be good to hear all of us)

    DBCC CHECKDB on an Huge Database Bcz it may depends on various factors,

    DB size,

    Load on the server-(I/O activity ,memory,No of users operation on that Database,CPU busy. Etc..)

    Thanks and Regards,

    RamaUdaya.K

  • October 26, 2010 at 3:47 am

    #1242112

    I was able to find the estimated_completion_time on a SPID which was in SUSPENDED state.

    percent_complete estimated_completion_time status

    ---------------- ------------------------- ------------------------------

    99.64832 77066 suspended

    Not sure about the situation you were in.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • October 26, 2010 at 1:08 pm

    #1242513

    Hi Pradeep,

    I beleive in my case it is different and I was tracking the same during that time.

    let me Await for that type of situation once again and I will observe.

    Thanks and Regards,

    Rama udaya.K

  • October 26, 2010 at 1:44 pm

    #1242527

    Maybe Paul Randal has more info on that at http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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