Hi.

Some problems right now, one server, a part of a 2 node always on cluster, had problems, I had to restart.

I'm getting this error for example now:

Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

+

SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

Now, on the instance I have activated maximum servere memory, my question; Is it possible to adjust this setting without starting the service?

Some parameter for example?

The server itself have enough memory.

 you can increase the sql server memory without any restart...what edition of sql are you using?

--20 GB memory
EXEC sys.sp_configure N'max server memory (MB)', N'20480'
GO
RECONFIGURE WITH OVERRIDE
GO

how many connections do you have know?? check this article on max worker thread 

https://technet.microsoft.com/en-us/library/ms187024%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

To add to what Stan said, just remember that the max server memory is specified in MEGABYTES, not GIGABYTES. (I am speaking from experience, don't make fun of me now).

Also, if you reduce the max memory, you may not see the usage come down immediately, but it eventually will come down (without you having to restart the server). Same with increasing the memory.

If my memory serves me right, the max memory you specify is the amount of memory that buffer pool is allowed to use for SQL 2008R2 and earlier, but it is total memory for SQL 2012 and later.  Taking that into consideration, if you are increasing the memory, don't increase it to a value where there is nothing left for OS and other tasks that may need to run.

Hi, maybe you misunderstood, the service would not start, and i found out why:

I had set the max memory to the minimum by a mistake (128mb)

I found a way to start the sql server anyway (with single user mode) and minimal settings:

add the -f parameter.

(http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-services-fail-to/)

Then i could log in and change the setting.

:) :)

• Marked as answer by niklasrene 8 hours 48 minutes ago