What is the min and max value of can hold in SQL Server process

Question

I have a server where in I can upload and retrieve data fast when my server is in (take offline) mode but if I live the server (with 150 + process) in SQL Server, I get an error from the application:

The server was unable to process the request due to an internal error.
For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs.

This is my screenshot of the server

and this is the error in Vb.net application

Error Logs

Date,Source,Severity,Message
12/05/2017 17:01:09,spid139,Unknown,CHECKDB for database 'SALESANDTRACKING07272017' finished without errors on 2016-06-07 00:00:09.867 (local time). This is an informational message only; no user action is required.
12/05/2017 17:01:06,spid139,Unknown,Starting up database 'SALESANDTRACKING07272017'.
12/05/2017 17:01:06,spid139,Unknown,Setting database option ONLINE to ON for database SALESANDTRACKING07272017.
12/05/2017 17:00:51,spid68,Unknown,Setting database option OFFLINE to ON for database SALESANDTRACKING07272017.
12/05/2017 17:00:51,spid68,Unknown,Process ID 139 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 17:00:51,spid68,Unknown,Process ID 105 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 17:00:51,spid68,Unknown,Process ID 53 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 17:00:44,spid68,Unknown,CHECKDB for database 'SALES07272017' finished without errors on 2016-06-07 00:00:09.867 (local time). This is an informational message only; no user action is required.
12/05/2017 17:00:40,spid68,Unknown,Starting up database 'SALES07272017'.
12/05/2017 17:00:39,spid68,Unknown,Setting database option ONLINE to ON for database SALES07272017.
12/05/2017 17:00:35,spid203,Unknown,Setting database option OFFLINE to ON for database SALES07272017.
12/05/2017 17:00:35,spid203,Unknown,Process ID 145 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 17:00:35,spid203,Unknown,Process ID 134 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 17:00:35,spid203,Unknown,Process ID 68 was killed by hostname DESKTOP-NAVEQ78<c/> host process ID 18060.
12/05/2017 16:41:57,spid70,Unknown,The client was unable to reuse a session with SPID 70<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
12/05/2017 16:41:57,spid70,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.
12/05/2017 16:37:40,spid121,Unknown,The client was unable to reuse a session with SPID 121<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
12/05/2017 16:37:40,spid121,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.
12/05/2017 16:24:36,spid66,Unknown,The client was unable to reuse a session with SPID 66<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
12/05/2017 16:24:36,spid66,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.
12/05/2017 16:23:14,spid61,Unknown,CHECKDB for database 'SALESANDTRACKING' finished without errors on 2016-06-07 00:00:09.867 (local time). This is an informational message only; no user action is required.
12/05/2017 16:23:13,spid61,Unknown,Starting up database 'SALESANDTRACKING'.
12/05/2017 16:23:13,spid61,Unknown,Setting database option ONLINE to ON for database SALESANDTRACKING.
12/05/2017 16:07:20,spid61,Unknown,Setting database option OFFLINE to ON for

please see this screen shot from the server (memory usage)

Process in the server

https://ibb.co/nA2AHw

Please add more information, this is too broad to answer as it stands. — Tanner, Dec 05 '17 at 09:01
>>> I can upload and retrieve data fast when my server is in (take offline) <??? You can receive the data from server that "is offline"??? — sepupic, Dec 05 '17 at 09:01
Check sql server error log for the errors and update your question with this information — sepupic, Dec 05 '17 at 09:02
This depends (as far as I am informed) on the number of cores you have in your server and (maybe) the amount of memory... — Tyron78, Dec 05 '17 at 09:08
I there anything I can do about it? I just duplicate database so on but that error is always appearing — Regime Evangelista Lesmoras, Dec 05 '17 at 09:14
Your memory is pretty full - I guess any further process will crash. Check which processes / applications are running - maybe you can close some background processes... — Tyron78, Dec 05 '17 at 09:35
Is the memory be the problem? because I try to set the mode of my server to offline to test if I can retrive and upload data and it works fine but if I live the server and all the client side application are running the error comes up. — Regime Evangelista Lesmoras, Dec 05 '17 at 09:40
Is it ok to have much ram in server like I will upgrade it to 64 or 160gb of ram? and if the users grows is the server take more memory? — Regime Evangelista Lesmoras, Dec 05 '17 at 10:10
"I try to set the mode of my server to offline to test if I can retrive and upload data and it works fine". how can you possibly retrieve data when your server is _offline_?? Please clarify. Unless the application is not actually using the database at all? Is the VB.NET application on the same machine as the database server? — ADyson, Dec 05 '17 at 10:10
yes its offline and I use another database(local only) so I can retrieve and upload data using vb.net application and services.. by the way I offline the live database so that I am the only one who use the server and the local database. upon testing that the application works fine. — Regime Evangelista Lesmoras, Dec 05 '17 at 10:20
so what you really mean by making it "offline" is you switch the app to point to the local database instead of the server one? Also as I mentioned, is the VB.NET app located on the same machine as the SQL server? — ADyson, Dec 05 '17 at 10:51
yes it point to local database and the sql server and vb.net was in separated machines — Regime Evangelista Lesmoras, Dec 05 '17 at 11:01
Ok. So earlier you asked "Is the memory be the problem? because I try to set the mode of my server to offline to test if I can retrive and upload data and it works fine". So yes it seems clear that the memory on the SQL server is causing a performance problem. Now you need to figure out what is eating all that memory. — ADyson, Dec 05 '17 at 12:48
yeah but you need to find out what is using it all up. Perhaps an app is leaking memory or not closing connections properly, or someone is running some very complicated queries or something. Exactly how many users are connecting to this server simultaneously? It's not obvious that this should make it run out of memory unless it's a large number. Just adding more memory isn't a sustainable or permanent fix IMO. — ADyson, Dec 05 '17 at 19:41
@ADyson I use hardcoded codes from my application and now it use 400+ users around the branches in the company that I am employed with.. do you think It might be the problem? and I check the server processes and I see that the Sql Windows takes to much process i updated my screensht — Regime Evangelista Lesmoras, Dec 06 '17 at 07:38
400, if they're simultaneously connected, could certainly keep it busy. But I doubt they'll all be actually using it at the same moment. Depending exactly what they're doing their processes might use memory, but it should get freed up as well. Right now the live DB server I'm working on has 186 _active_ connections across 23 DBs and using approx. 19GB RAM out of an installed total of 20GB. Total possible users is theoretically 900. But it never runs out of RAM. I can't be more specific but I'd guess something is leaking memory or using too much, or not closing connections properly. — ADyson, Dec 06 '17 at 10:01
Ahh ok. I think i must ccheck. For. any open connctions. In my applications — Regime Evangelista Lesmoras, Dec 06 '17 at 12:10

Regime Evangelista Lesmoras · Accepted Answer · 2018-01-16T05:15:54.670

whenever I try to upload data in the server it goes to the servicereference.channelafter that it goes to the api apibilling but the thing here whenever the application sends data to the services and api then from there to server the pooling of process in the server makes the consuming of the memory and for that the error will occur because it cannot handle the other connection in the application

try to change my connection string to my other server with smaller process and it works... I conclude that the memory is the problem I will upgrade the memory of the server to 64gb.. by the way my current server has 32 ram on thanks for all the help :D it seems that the error is not on the Api or service behavior , it's the memory that causes the problem

and as far as I know on the server you must upgrade the memory if the database will expand fast that there are new users that are currently login in the application then it will have consume another memory.

'=========Updated Answer December 13, 2017 1:16 am------------------

I browse the internet and I search DataAdapter vs. DataReader and I found out that DataAdapter goes to memory cache while the DataReader The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory.

I get that to this link
http://www.dotnetcurry.com/aspnet/143/convert-data-reader-to-data-table

I use dataadapter when retrieving data so It happens that 150 + process will consume a lot of memory cache

'=========Updated Answer January 16, 2018 1:16 am------------------ My solution from this problem is Indexing

I read from this

What is an index in SQL?

that indexing has the ability to retrieve large amount of data in an instant

but beware to use indexing

I just read this

https://www.sqlpassion.at/archive/2016/03/29/clustered-indexes-advantages-disadvantages/

if you use many indexes the adding,uploading and deleting of data will be slowed