December 4, 2018 at 1:27 pm
I upgraded seven packages from SQL Server 2008 to 2016. The packages run fine when running each one individually from within Data Tools, but when I run them in the SQL Agent Job they can run for 12 hours or longer or even just simply fail. The agent job in 2008 runs in about 10 minutes. I"m at a loss as to how to solve this problem and need some ideas. The required databases from 2008 have been upgraded to 2016 and restored, all indexes and everything else came with it.
I need ideas here. Have any of you experienced similar issues after upgrading?
December 4, 2018 at 1:37 pm
Polymorphist - Tuesday, December 4, 2018 1:27 PMI upgraded seven packages from SQL Server 2008 to 2016. The packages run fine when running each one individually from within Data Tools, but when I run them in the SQL Agent Job they can run for 12 hours or longer or even just simply fail. The agent job in 2008 runs in about 10 minutes. I"m at a loss as to how to solve this problem and need some ideas. The required databases from 2008 have been upgraded to 2016 and restored, all indexes and everything else came with it.I need ideas here. Have any of you experienced similar issues after upgrading?
Did you change the compatibility levels of the databases after restoring them?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 4, 2018 at 2:25 pm
Phil Parkin - Tuesday, December 4, 2018 1:37 PMDid you change the compatibility levels of the databases after restoring them?
We did, the compatibility levels are all 130 which is SQL Server 2016
December 4, 2018 at 2:46 pm
Polymorphist - Tuesday, December 4, 2018 2:25 PMPhil Parkin - Tuesday, December 4, 2018 1:37 PMDid you change the compatibility levels of the databases after restoring them?We did, the compatibility levels are all 130 which is SQL Server 2016
OK, so the new cardinality estimator is in play and is likely to be having an effect.
You can easily test this by setting the levels back to 2012 and rerunning your SSIS packages.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply