July 11, 2018 at 7:43 am
dropsa999 - Tuesday, July 10, 2018 10:08 PMastrid 69000 - Wednesday, July 4, 2018 2:40 PMDo you mean to enable the login for the the ssis package?
I will check that out.
Last night also stopped after working for 4 days straight and after I restarted the server (twice), it worked perfectly.
We did have the same issue before with a job that was calling directly a store procedure.
Maybe the servers are hunted :).I too have the same issue with 2012 Server, the job runs forever without invoking the SSIS package, restarting the SSIS services and Agent Services is the only option which fixes the issue every time, tried every suggestion posted in the comments but nothing fixed it.. thought it was a bug in 2012 I don't know that it still exists in 2017.
There are actually quite a few posts on different forums with the issue. And it doesn't seem to be limited to any specific version. Problem is that there doesn't seem to be any one specific issue for all of them. It used to be mostly due to permissions issues or something in the package prompting for user input. But a restart generally wouldn't result in success in those cases. Not being able to connect to AD maybe but that should be in the windows event logs.
The other issues noted have been blocking, not enough resources available (memory, CPU). Most post have reporting no blocking and no errors anywhere. And often it's reported that it's intermittent and a restart corrects the issue. With all of that, I'd think resources or possibly something in one of the code paths in the package might be the issue. Resource issues may likely be found querying the system_health extended event or querying the ring buffer. Rarely does any one query those even if suggested.
Sue
July 11, 2018 at 12:48 pm
We are still looking for solution and trying everything you suggested. I will update once we have something, even an error will help at this point :doze:
July 11, 2018 at 1:07 pm
astrid 69000 - Wednesday, July 11, 2018 12:48 PMWe are still looking for solution and trying everything you suggested. I will update once we have something, even an error will help at this point :doze:
Thanks...please do keep us updated. It's such a weird issue. I've never seen a resolution to the ones with no blocking, restart fixes, intermittent...like yours.
Sue
August 3, 2018 at 6:40 am
Hi,
I think we did find a solution, it has been working for a couple of weeks without any problem.
We added more RAM and that stopped the jobs from running forever and ever.
In a virtual environment you like ours that is very crowded, we were always waiting for something, to write to the disc, to read from the disc, for cpu etc etc.
We went from 32 to 64 and on the ssis jobs I limited the amount of cpu they can use.
and jobs have been running, both the ssis jobs that were giving some issues and the t-sql job that was also giving issues.
So from my own personal experience. first add RAM.
August 3, 2018 at 9:01 am
Just a word of caution from a crusty ol' DBA that's long in tooth and has many battle scars...
Adding RAM is always nice but that may not be the correct fix and tends to lead people into complacency. You need to look at the code and the data and the execution plans, etc, and figure out why it needs so much RAM (and other resources) or it will all just come around again as the data scales up over time. Now, it may very well be that the code is as efficient as possible but I've found that's usually not the case and, if the problem occurs again, you've just gotta know it will occur at the times you can least afford for such problems to occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2018 at 11:18 am
Hi,
it is not the it needs to much ram, rather that I am in a virtual environment and I have shared everything.
The jobs did run 5 our of 7 times more or less, therefore running the jobs was not an issue when the virtual machine was not in the line for resources.
So by adding ram, the virtual machine doesn't max up and while it wait, it is not chocking.
August 3, 2018 at 12:56 pm
astrid 69000 - Friday, August 3, 2018 11:18 AMHi,
it is not the it needs to much ram, rather that I am in a virtual environment and I have shared everything.
The jobs did run 5 our of 7 times more or less, therefore running the jobs was not an issue when the virtual machine was not in the line for resources.
So by adding ram, the virtual machine doesn't max up and while it wait, it is not chocking.
I think you may have missed the point though.
RAM can be seen as one of the greatest SQL Server band-aids. It's cheap and can cover up a lot of problems. Even when the problems are still there. In one of your posts you said:
stopped after working for 4 days straight and after I restarted the server (twice), it worked perfectly.
So it worked for awhile after reboots. And then starts failing. You would wonder why wasn't it failing after the reboots if the amount of ram is the same after the reboot as it is days later. The description of the VM and resources almost sounds like there is no reservation for the memory and there is contention with other VMs. That sounds more like a VM configuration issue.
Sue
August 3, 2018 at 1:05 pm
you are probably right, i did think it was a configuration issue, but it is a hard bargain with the people that made the vm 😀
i just needed my environment to work daily and that made it work.
August 3, 2018 at 1:15 pm
astrid 69000 - Friday, August 3, 2018 1:05 PMyou are probably right, i did think it was a configuration issue, but it is a hard bargain with the people that made the vm 😀
i just needed my environment to work daily and that made it work.
That unfortunately happens too often. If you have anyone in "your corner", I'd try to get them involved. Try getting the VM configuration (especially with memory) information. After that, there are a few best practices type of articles for virtual SQL Servers. For the configuration that don't match, involve whoever might help in your corner. And the memory issues is in all of them I've read. Probably not in all, but most.
But sometimes politics prevent even doing that - sorry if that's the case for you. Those are not fun to deal with.
Sue
August 3, 2018 at 1:17 pm
Just read through this topic, and I think Sue is on the right track.
When you commented "rather that I am in a virtual environment and I have shared everything" that started ringing the alarm bells.
One thing I would suggest (not knowing anything about your virtual environment) would be to go to the VM admins with this link to the VMWare best practices for MS SQL Server.
When I first started at my current job, I had to use this to get the VMWare admins to turn off ballooning memory for my servers. What I'd try in your case would be, if they turn off any sort of memory ballooning (different VM systems use different terms,) you'll agree to step the RAM back down to 32 or 16GB of RAM initially. It didn't sound like, other than the SSIS job hanging, that you were having other performance issues, so the customers shouldn't have an issue.
August 3, 2018 at 1:44 pm
A echo the VM settings as a place to start.
Also, how are the SSIS packages deployed? Is the SSISDB being cleaned up? We have an SSIS package that literally fills the drives with logging. The server exhibits the same symptoms you describe. It would just sit, and it appeared nothing was happening until the logs were cleared.
By default, a job named "SSIS Server Operation Records Maintanance" is created when you deploy an SSIS package.
The logging settings for SSIS may need to be changed, the parameters for retention of the logs may need to be changed, and the job may need to be run more frequently.
Under "Integration Services Catalogs", right click on the "SSISDB" catalog, and look at the properties. That's the default name, yours may be different.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 3, 2018 at 2:56 pm
The issue was not only with one of my ssis packages but also with a t-sql job.
I have changed the retention time of the ssis.
I had also running the first aid kit from Brent Ozar and everything pointed to the sql server waiting for stuff.
August 4, 2018 at 1:55 am
Do you have the max memory setting configured in SQL Server and leaving a reasonable portion of the RAM for processes other than SQL Server? One possibility is that your SQL workload is hogging all the RAM and starving other processes like SSIS to the point they're just thrashing about swapping pages between disk and RAM - which can manifest as seemingly "doing nothing" if you look at CPU load alone.
August 4, 2018 at 9:42 am
I can't help much because I'm not the one that sets up the VMs where I work, but I agree with the others. I've seen what happens when such resources as memory and CPU are shared and it's not a pretty sight. Also, make sure that someone doesn't have any power management options turned on. It needs to be set to the "full blast/it's all mine" setting.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2018 at 9:53 am
andycadley - Saturday, August 4, 2018 1:55 AMDo you have the max memory setting configured in SQL Server and leaving a reasonable portion of the RAM for processes other than SQL Server? One possibility is that your SQL workload is hogging all the RAM and starving other processes like SSIS to the point they're just thrashing about swapping pages between disk and RAM - which can manifest as seemingly "doing nothing" if you look at CPU load alone.
Yes we have a max configures and we go half and half.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply