ERROR when Editing Job Steps

Question

Post reply

ERROR when Editing Job Steps

ktflash

Default port

Points: 1400

December 10, 2021 at 10:51 am

#3962333

Ahoi,

when im trying to edit jobs i get a weird error.

EDIT: Restarting the server removes the problem, but its not telling me what is the cause

===================================

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached. (System.Data)

------------------------------
Program Location:

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.DataTransformationServices.DTSExecUI.Controls.GeneralViewCtrl.GetSSISPackage()

What i have done:

Checked the number of connections for each DB with sp_who, query (highest value was 60, 31 after killing inactive ones)
Checked Max number of connetions for each DB (still default 32767)
Killed Queries that have not been active within 12 hours (https://blog.sqlauthority.com/2019/05/06/sql-server-script-to-kill-all-inactive-sessions-kill-sleeping-sessions-from-sp_who2/)

It seems like its like the number of connections is not the issue (????), but i still get the error .

When trying to edit a step i see the following empty windows instead of actual information (environment, parameters).

2021-12-10 11_48_30-sv-04sql - Remotedesktopverbindung

I have never seen this issue before only now when we migrated our sql server (also upgrading 2016 to 2019).

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24690 More actions · Answer 1

What I would check, and what I expect the problem is, is the "Package" tab. What is probably happening or happened is that the SQL instance you are configuring the job for doesn't have access to the location of the SSIS package. Hence the timeout.

I would verify that screen and make 110% sure it is set up correctly, and even if it looks correct, I would try setting those parameters again to make sure that it can actually see the package.

I am 90% confident the problem is NOT with the "Configuration" tab, that is just the symptom.

What likely happened is when you upgraded from 2016 to 2019, something SSIS related got changed. It MAY be package locations on the server, it may be server names, it may be the port, or something else. I would check that the "package" tab is set up correctly and if that isn't the problem, I would double check that the SSIS service is running on your server and that it wasn't missed in the database upgrade.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

This has nothing to do with connections to the database. It's a connection POOLING issue.

Check this out. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

A simple Google search of "This may have occurred because all pooled connections were in use and max pool size was reached. (System.Data)" will return many hits and answers.

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/