SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job in hung state..How to fix


Job in hung state..How to fix

Author
Message
coolchaitu
coolchaitu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 1166


Good morning Experts,
There is a job in hung state as shown above. Please help in troubleshooting this
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
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
coolchaitu
coolchaitu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 1166
John Mitchell-245523 - Monday, July 17, 2017 4:00 AM
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?

John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35055 Visits: 16670
So it's not hung as such, it's just taking longer? What has changed? Do you have more data than you had before? Do you maintain your statistics and indexes with the same frequency as you did before (that may or may not make a difference to DBCC run duration)? Is there any other activity on the database or server or disks at the same time as the job runs? Do you have plenty of free space on the drives on which your database files reside in case of any growth in the files for the internal snapshot that the DBCC process creates?

John
coolchaitu
coolchaitu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 1166
John Mitchell-245523 - Monday, July 17, 2017 5:05 AM
So it's not hung as such, it's just taking longer? What has changed? Do you have more data than you had before? Do you maintain your statistics and indexes with the same frequency as you did before (that may or may not make a difference to DBCC run duration)? Is there any other activity on the database or server or disks at the same time as the job runs? Do you have plenty of free space on the drives on which your database files reside in case of any growth in the files for the internal snapshot that the DBCC process creates?

John

Hi John,
The database grew by 79686 MB. But just for 79686 MB data, DBCC CHECKDB taking 10 more hours of time is not satisfying me.

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99243 Visits: 33014
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
coolchaitu
coolchaitu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1621 Visits: 1166
Grant Fritchey - Monday, July 17, 2017 8:07 AM
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.

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99243 Visits: 33014
coolchaitu - Monday, July 17, 2017 9:24 AM
Grant Fritchey - Monday, July 17, 2017 8:07 AM
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217366 Visits: 41991
coolchaitu - Monday, July 17, 2017 4:46 AM
John Mitchell-245523 - Monday, July 17, 2017 4:00 AM
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?
Grant Fritchey - Monday, July 17, 2017 11:21 AM
coolchaitu - Monday, July 17, 2017 9:24 AM
Grant Fritchey - Monday, July 17, 2017 8:07 AM
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?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Marsha
Marsha
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 442
Can you check the auto growth on the database to see what it is set to?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search