3

We have an application that uses TomCat and Java to receive files and move them to a SQL Server 2012 instance.

Previous versions of this software worked just fine.

The new release fails on our system with the error

Error creating connection pool for dbid:

Many hours have been spent adjusting the configuration to get past this error with no positive result. Any insight on what the error means and how it might be traced would be appreciated.

  • Is there any text after the dbid: part of the message? – Max Vernon Mar 13 '18 at 14:41
  • Also, have you looked at the SQL Server error log for connection problems? – Max Vernon Mar 13 '18 at 14:41
  • The text after dbid is the name of the database. We see no errors in the SQL Server error log. – Bruce Riddle Mar 13 '18 at 17:03
  • Is this custom software written by your developers, or is this some off-the-shelf software provided by a vendor? – Max Vernon Mar 13 '18 at 17:07
  • Also, what does this show for connections that exist at the server? SELECT COUNT(1) FROM sys.dm_exec_connections; – Max Vernon Mar 13 '18 at 17:10
1

Connection Pooling occurs completely at the client-side of an application, is seldom affected by the SQL Server directly.

Connection pooling depends on parameters in the connection string used by the client to connect to the SQL Server. Identical connection strings are typically candidates for pooling, assuming the following connection string settings allow pooling:

╔════════════════╦═══════════════╦═════════════════════════════════════════════════════════════╗
║    Setting     ║ Default Value ║                         Description                         ║
╠════════════════╬═══════════════╬═════════════════════════════════════════════════════════════╣
║ Max Pool Size  ║          100  ║ The maximum number of connections allowed in the pool.      ║
║ Min Pool Size  ║            0  ║ The minimum number of connections allowed in the pool.      ║
║ Pooling        ║            1  ║  Enable connection pooling.  0 Disables connection pooling. ║
╚════════════════╩═══════════════╩═════════════════════════════════════════════════════════════╝

The SQL Server may be running out of service worker threads, if existing connections are not closed properly, which would typically result in connection problems. If the max worker threads server configuration option is set incorrectly, or too low, that might be a problem. The default value for this option is 0, indicating that SQL Server will automatically configure the value, according to these rules:

╔══════════════════╦══════════════════╦═════════════════╗
║ Number of CPUs   ║ 32-bit computer  ║ 64-bit computer ║
╠══════════════════╬══════════════════╬═════════════════╣
║ <= 4 processors  ║             256  ║             512 ║
║ 8 processors     ║             288  ║             576 ║
║ 16 processors    ║             352  ║             704 ║
║ 32 processors    ║             480  ║             960 ║
║ 64 processors    ║             736  ║            1472 ║
║ 128 processors   ║            4224  ║            4480 ║
║ 256 processors   ║            8320  ║            8576 ║
╚══════════════════╩══════════════════╩═════════════════╝

You can check the configured value using this query:

SELECT c.name
    , c.value
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'max worker threads';

Based on the error message, it looks like your client end is configured incorrectly, but that is only a guess. Check with your developers or vendor for help determining the cause of the problem.

  • This is helpful. I think the vendor, who maybe has 50 hours in this effort and is about to give up. The software is used nationally and the previous version worked fine for years. – Bruce Riddle Mar 13 '18 at 18:36
  • I'm glad to help. Connection pooling can be painful sometimes. – Max Vernon Mar 13 '18 at 18:46
  • @BruceRiddle - did you get this sorted out? – Max Vernon Mar 19 '18 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.