"This may have occurred because all pooled connections were in use and max pool size was reached"

adamras

Good morning,

I am having a significant issue with NPM and have been for quite some time. I have a ticket open, 129840.  The issue that I am having is that every 3-4 days NPM stops alerting that devices are down.  It also stops showing that devices have dropped in the webconsole.  How I discover that the sever is no longer performing is when I go to view "Events" though the webconsole I get the error about the Business Layer and job engine.  (My apologies I don't have the error verbatim) Throughout the last couple of months of trying to fix this issue we have discovered that the issue starts when the "Alert Engine" in the Solarwinds event log errors.  This Alert Engine has 2 distinct errors.  They are as follows:

EventID 4001

[AlertCheckingThread] ERROR All - Service was unable to open new database connection when requested.
Exception 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.
Connection string - Data Source=(local);Initial Catalog=NetPerfMon;User ID=SolarWindsNPM;Password=*******;Connect Timeout=1200;Load Balance Timeout=120;Application Name="Orion Alerting Engine";Workstation ID=NPM01

and also

EventID 0

[AlertCheckingThread] ERROR Error - Error in ExecuteSQLAlert() -
System.NullReferenceException: Object reference not set to an instance of an object.
   at AlertingEngine.CheckAlert.ExecuteSQLAlert()

When this is the case the only remedy is a reboot of the server.  As you can imagine rebooting a production server twice a week is not a plan that is long term or appreciated by management. 
I have read a couple of things about setting the connection time out to 30sec but I don't know if that is a good idea.  I am quite weak with SQL and worry that I am going to make a change that will render the whole box useless.  I am unable to determine why this would be the case only in the middle of the night, not correlating to Maintenance of the SQL server. 
There are no Scheduled Tasks taking place at the time of failures. 
With checking perfmon many times it's not showing that there are any issues with anything.  There was a slight jump in i/o once in a while but not anything that is too significant. 

I am currently running NPM 9.5 SP4 with the hotfix2 installed. 
SQL 2005
Windows 2003 Server
Any and all assistance would be appreciated. 
Please, if you require more information about anything let me know and I will facilitate to the best of my ability.

Thank you in advance,

--adam

adamras

o.. good news we think.

A couple months ago I had discovered that there were a lot of error on the swdebugMaintenance.log file in regards to our Wireless tables.  They were as follows:

 

2010-01-29 02:29:13,807 [1] ERROR SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL - Failed to execute procedure: NPM_HST_WL_DetailToHourly
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NPM_NV_WL_CLIENTS_HOURLY_NPM_NV_WL_INTERFACES_HOURLY". The conflict occurred in database "NetPerfMon", table "dbo.NPM_NV_WL_INTERFACES_HOURLY".
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL.InternalExecuteStoredProc(String procedureName, SqlParameter[] parameters)

At times these errors were causing my maintenance to take 23 hours.  Most often it would finish the maintenance normally, but I am mentioning it because it was a symptom.

These were showing up basically since we installed NPM 9.5 where the Wireless module was no longer on it's own.  I had mentioned this a couple of times but I was told not to worry about it or to just run some clean up queries. 

This week when combing through te system to find the root cause of these problems the tier 3 tech discovered this errors and requested that we purge all the rows from the Wireless tables and start over. 

The process I followed was; Stop the 3 services in Orion Service manager (Job Scheduler, Job Engine, Orion Module Engine)
 
Now launch the database manager and run the following queries
 
delete from NPM_NV_WL_APS - hit refresh
delete from NPM_NV_WL_APS_DETAIL - hit fresh
 
So on and so forth through all the wireless tables
 
If you happen to time out in the database manager - Open up SQL Server Management Studio and run the same commands in there (should not time out)
 
Once you get through all the queries - go to C:\Documents and Settings\All Users\Application Data\SolarWinds\Job Engine\Data folder and rename or delete the jobengine35.sdf file
 
Then go to the C:\Documents and Settings\All Users\Application Data\SolarWinds\Installers directory and run the jobengine.msi (and perform a repair)
 
Then go back to the Orion Service Manager and start the Orion Module Engine

Since then.. no more issues.  No more maintenance errors, no hang ups, no Business Layer issues. 
A problem that has plagued this system for months has been resolved.  Thank you to my tier 3 tech, David.
I appreciate your help.

I hope this information helps someone, as it sure was terrible explaining why I had to reboot the NPM box every 3 days.

 

Thanks,
--adam