You've been around long enough to know that you haven't provided nearly enough information. What does the job do? What makes you think it's hung? How long does it normally take and how long has it taken so far this time? Does the same thing happen if you run the same command(s) outside of the job?
John
Hi John,
The job does DBCC CHECKDB on a single database.It runs every saturday. It usually takes 21 hours to complete, but for the last 2 saturdays, it took 1 day 7 hours 32 minutes to complete. How to find out why it took longer time?
So what's the size of the database now?
So monitor the server. Capture the wait statistics of this process to understand where and why it's running slow. None of us without any kind of knowledge of your server, access to it, or the hyper-minimal amount of information you've provided are going to go "Oh, that's the dinglefarb setting. Change it from "purple" to "42" and everything will run faster."
Here's an example on how to capture wait statistics for a single query. Gather those metrics and maybe we can make some suggestions. I would also suggest you monitor for blocked processes. It could be that this process is simply waiting on others as well as waiting on resources.
HI Grant,
Please let me know what information you require.
So, considering I say above "Capture the wait statistics of this process" and "Here's an example" and "Gather those metrics", I'm actually seriously confused by this follow-up question.
To add to that, what is the size of the database now?