July 4, 2016 at 10:55 am
Hi,
I am not sure if this is a unique issue for me or is it a known issue for others as well. Basically, they have an SSIS package which connects to the source server say A, and when it executes that step it gets timed out for some reason when run as per schedule at 1:00a. However, when the same job runs manually it completes successfully. Is there any reason what would be the cause of this issue, and how would I find out what would have been causing it.
Regards,
Feivel
July 4, 2016 at 11:03 am
ffarouqi (7/4/2016)
Hi,I am not sure if this is a unique issue for me or is it a known issue for others as well. Basically, they have an SSIS package which connects to the source server say A, and when it executes that step it gets timed out for some reason when run as per schedule at 1:00a. However, when the same job runs manually it completes successfully. Is there any reason what would be the cause of this issue, and how would I find out what would have been causing it.
Regards,
Feivel
So you are talking about a single SQL Agent job? And not running the package manually from SSDT?
Have you tried scheduling the job at other times of the day (to make sure that there's nothing weird happening to the source server at 1am)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 4, 2016 at 11:23 am
Phil Parkin (7/4/2016)
ffarouqi (7/4/2016)
Hi,I am not sure if this is a unique issue for me or is it a known issue for others as well. Basically, they have an SSIS package which connects to the source server say A, and when it executes that step it gets timed out for some reason when run as per schedule at 1:00a. However, when the same job runs manually it completes successfully. Is there any reason what would be the cause of this issue, and how would I find out what would have been causing it.
Regards,
Feivel
So you are talking about a single SQL Agent job? And not running the package manually from SSDT?
Have you tried scheduling the job at other times of the day (to make sure that there's nothing weird happening to the source server at 1am)?
Yes this as a single SQL Agent job, and it runs fairly well when scheduled at other times or even when run manually, but the problem remains when the scheduled run kicks at the said time. I am not sure what would be the issue. Basically, it throws the error as follows.
11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:00:02 AM Could not load package "x" because of error 0x80004005. Description: Query timeout expired Source: Microsoft SQL Server Native Client 11.0 Started: 1:00:02 AM Finished: 1:01:02 AM Elapsed: 59.998 seconds. The package could not be loaded. The step failed.
I am not sure what I should look for, and where. Because, this is not an issue with the package, but merely the timing, and we cannot change the times because the ETL needs to complete before the other process starts.
July 4, 2016 at 12:33 pm
ffarouqi (7/4/2016)
Phil Parkin (7/4/2016)
ffarouqi (7/4/2016)
Hi,I am not sure if this is a unique issue for me or is it a known issue for others as well. Basically, they have an SSIS package which connects to the source server say A, and when it executes that step it gets timed out for some reason when run as per schedule at 1:00a. However, when the same job runs manually it completes successfully. Is there any reason what would be the cause of this issue, and how would I find out what would have been causing it.
Regards,
Feivel
So you are talking about a single SQL Agent job? And not running the package manually from SSDT?
Have you tried scheduling the job at other times of the day (to make sure that there's nothing weird happening to the source server at 1am)?
Yes this as a single SQL Agent job, and it runs fairly well when scheduled at other times or even when run manually, but the problem remains when the scheduled run kicks at the said time. I am not sure what would be the issue. Basically, it throws the error as follows.
11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:00:02 AM Could not load package "x" because of error 0x80004005. Description: Query timeout expired Source: Microsoft SQL Server Native Client 11.0 Started: 1:00:02 AM Finished: 1:01:02 AM Elapsed: 59.998 seconds. The package could not be loaded. The step failed.
I am not sure what I should look for, and where. Because, this is not an issue with the package, but merely the timing, and we cannot change the times because the ETL needs to complete before the other process starts.
OK, so I was right in thinking that something must be happening to the source at 1am, which causes it to be unresponsive. Speak to the DBA for the source and describe your problem – ask whether they have any idea what might cause the source to be sluggish.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 4, 2016 at 1:49 pm
What does the error details for the job say? Perhaps modify the job to add step details to the logging process. Have someone stay up and monitor the server(s) involved when the job is actually running. I would use sp_whoisactive for that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 4, 2016 at 5:04 pm
Phil Parkin (7/4/2016)
ffarouqi (7/4/2016)
Phil Parkin (7/4/2016)
ffarouqi (7/4/2016)
Hi,I am not sure if this is a unique issue for me or is it a known issue for others as well. Basically, they have an SSIS package which connects to the source server say A, and when it executes that step it gets timed out for some reason when run as per schedule at 1:00a. However, when the same job runs manually it completes successfully. Is there any reason what would be the cause of this issue, and how would I find out what would have been causing it.
Regards,
Feivel
So you are talking about a single SQL Agent job? And not running the package manually from SSDT?
Have you tried scheduling the job at other times of the day (to make sure that there's nothing weird happening to the source server at 1am)?
Yes this as a single SQL Agent job, and it runs fairly well when scheduled at other times or even when run manually, but the problem remains when the scheduled run kicks at the said time. I am not sure what would be the issue. Basically, it throws the error as follows.
11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:00:02 AM Could not load package "x" because of error 0x80004005. Description: Query timeout expired Source: Microsoft SQL Server Native Client 11.0 Started: 1:00:02 AM Finished: 1:01:02 AM Elapsed: 59.998 seconds. The package could not be loaded. The step failed.
I am not sure what I should look for, and where. Because, this is not an issue with the package, but merely the timing, and we cannot change the times because the ETL needs to complete before the other process starts.
OK, so I was right in thinking that something must be happening to the source at 1am, which causes it to be unresponsive. Speak to the DBA for the source and describe your problem – ask whether they have any idea what might cause the source to be sluggish.
Phil - I work to support their in-house DBA team if there is an issue or if they need any assistance. The DBA's in-house could not understand what is going on, and hence, they've basically assigned this task to me, and I don't know what to look for. Any suggestions would be highly appreciated. I can't be awake every night to troubleshoot this if I can't figure out in a single slot.
July 5, 2016 at 4:37 am
I've had scheduled jobs (maintenance) not run at all if SQL VSS Writer is running.
July 5, 2016 at 5:51 am
If no one is prepared to hang around late & diagnose this while it's happening, the obvious alternative is to collect as much logging information as you can & then try to diagnose from that. Beyond that, I have no further ideas.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 5, 2016 at 6:00 am
I'd want the specifics on the error message.
If it's getting timed out, that means it's firing, so it's not an issue that it's an Agent job, or that the agent job or the schedule for the job are disabled in some way. Also the fact that you can run the job manually means that the job itself has the right permissions (although quadruple checking this is never a bad thing).
So, focus on the error and on what other processes are running during that period.What could be blocking it. Set up extended events to capture the wait stats for the process to understand what it is waiting on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 5, 2016 at 9:29 am
Grant Fritchey (7/5/2016)
I'd want the specifics on the error message.If it's getting timed out, that means it's firing, so it's not an issue that it's an Agent job, or that the agent job or the schedule for the job are disabled in some way. Also the fact that you can run the job manually means that the job itself has the right permissions (although quadruple checking this is never a bad thing).
So, focus on the error and on what other processes are running during that period.What could be blocking it. Set up extended events to capture the wait stats for the process to understand what it is waiting on.
Could you please list out the events that I can capture in order to log activities that happen on the source side, as oppose to being awake at night?
July 5, 2016 at 9:37 am
ffarouqi (7/5/2016)
Grant Fritchey (7/5/2016)
I'd want the specifics on the error message.If it's getting timed out, that means it's firing, so it's not an issue that it's an Agent job, or that the agent job or the schedule for the job are disabled in some way. Also the fact that you can run the job manually means that the job itself has the right permissions (although quadruple checking this is never a bad thing).
So, focus on the error and on what other processes are running during that period.What could be blocking it. Set up extended events to capture the wait stats for the process to understand what it is waiting on.
Could you please list out the events that I can capture in order to log activities that happen on the source side, as oppose to being awake at night?
sp_whoisactive has the ability to insert it's output into a table. Read Adam Machanic's 30-day blog post series on the script on SQLBlog.com.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 5, 2016 at 10:54 am
ffarouqi (7/5/2016)
Grant Fritchey (7/5/2016)
I'd want the specifics on the error message.If it's getting timed out, that means it's firing, so it's not an issue that it's an Agent job, or that the agent job or the schedule for the job are disabled in some way. Also the fact that you can run the job manually means that the job itself has the right permissions (although quadruple checking this is never a bad thing).
So, focus on the error and on what other processes are running during that period.What could be blocking it. Set up extended events to capture the wait stats for the process to understand what it is waiting on.
Could you please list out the events that I can capture in order to log activities that happen on the source side, as oppose to being awake at night?
yeah, I wasn't proposing being awake at night. That's why I suggested extended events. Here's a sample [/url]on how to set it up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2016 at 3:44 am
Grant Fritchey (7/5/2016)
ffarouqi (7/5/2016)
Grant Fritchey (7/5/2016)
I'd want the specifics on the error message.If it's getting timed out, that means it's firing, so it's not an issue that it's an Agent job, or that the agent job or the schedule for the job are disabled in some way. Also the fact that you can run the job manually means that the job itself has the right permissions (although quadruple checking this is never a bad thing).
So, focus on the error and on what other processes are running during that period.What could be blocking it. Set up extended events to capture the wait stats for the process to understand what it is waiting on.
Could you please list out the events that I can capture in order to log activities that happen on the source side, as oppose to being awake at night?
yeah, I wasn't proposing being awake at night. That's why I suggested extended events. Here's a sample [/url]on how to set it up.
Thanks! Grant. I read the blog, and that is awesome, but I am not looking for waits. Do you know of any specific counters or events that can come handy in my case. I hope I am not bothering you too much on this?
July 6, 2016 at 3:58 am
But you are looking for waits. You have a process that is timing out. That means it's running long. Why is it running long? Well, it's not getting the resources it needs. Why not? Not sure. What resources does it need? Not sure. How do we tell? Look at the wait statistics to determine what it's waiting to access.
You can also capture the run time on the queries involved in the process. That means capturing stuff at the statement level, but with the filters available in extended events, that should be pretty straight forward. I'd also capture the execution plans for those queries so you can identify any tuning opportunities. Capturing execution plans can be expensive, so, again, be sure you're applying filters to only capture information related to your process.
However, the wait stats are the big start point to understand why this process is timing out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2016 at 4:46 am
Grant Fritchey (7/6/2016)
But you are looking for waits. You have a process that is timing out. That means it's running long. Why is it running long? Well, it's not getting the resources it needs. Why not? Not sure. What resources does it need? Not sure. How do we tell? Look at the wait statistics to determine what it's waiting to access.You can also capture the run time on the queries involved in the process. That means capturing stuff at the statement level, but with the filters available in extended events, that should be pretty straight forward. I'd also capture the execution plans for those queries so you can identify any tuning opportunities. Capturing execution plans can be expensive, so, again, be sure you're applying filters to only capture information related to your process.
However, the wait stats are the big start point to understand why this process is timing out.
Thanks! Grant. Makes all the sense in the world.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply