Sql-server – Grant non Sysadmin the ability to change max memory setting SQL Server

I was able to reset the memory by establishing a DAC connection and without ever resorting to the -f startup parameter. However, I had to be persistent:

• At 128 MB, you will get all kinds of errors in the error log:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

(repeat for other caches)

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Verbose DBCC MEMORYSTATUS dumps

errors about audit / extended events data potentially lost

There is insufficient system memory in resource pool 'internal/default' to run this query.

• In some cases, the errors will be persistent enough to simply shut SQL Server down (and nothing will be recorded in the log). This happened twice during my experiments, and may also be influenced by the size of the databases attempting to be recovered on subsequent restarts. You should check whether any of the errors you receive while attempting to establish a DAC connection actually stem from the service not running (or perhaps in the process of shutting down).
• In each case, I had to go to Configuration Manager and restart the service. On the second try, I think the service shut down (or was in the process) before I established the DAC connection. On the third try, there was a lot less recovery action on startup, and I was able to establish a DAC query window and adjust max server memory, then when I restarted, everything went back to normal.
• Note that I was using 11.0.2376, and I recommend you perform this kind of edge case testing on builds you are likely to run in production. (Things like this may have been fixed post-RTM, and not all fixes are public.) If you're running RTM in production, then I kind of take that back, and will suggest that you reconsider that approach.

If you find that in your case you are also experiencing this "oops, SQL Server is no longer running" symptom, you could probably attach a debugger and determine exactly how SQL Server is shutting down.

So, can you do it without -f? Yes. Is it any cleaner? Only if you're really lucky. If your databases are large you are more likely to want to have a predictable number of restarts (2) because the recovery process could be a lot more involved than mine

Of course, an even cleaner thing would be to not mess around with max server memory using the GUI or ad hoc scripts that are prone to fudges - in production you want use a second set of eyes and have proper, validated, source controlled scripts that are much more predictable than "playing around." :-)

The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.

There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:

Using locked pages in the memory manager

Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:

Whereas Resource Monitor shows:

Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.

There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.

It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.

Generally speaking, locking pages in memory is a good thing.