SQL SERVER – Adjust Memory with Dedicated Administrator Connection (DAC)

Recently one of my clients of Comprehensive Database Performance Health Check accidentally set max server memory to a very slow value. This created a lots of issues for them and after a while, their server stopped. When they started to restart the services, the services did not start at all. Let us learn today how to Adjust Memory with a Dedicated Administrator Connection (DAC).

The way to increase the memory is very simple and you will have to use Dedicated Administrator Connection (DAC). SQL Server makes every attempt to make DAC connect successfully, but sometimes, it may not be successful.

Let us learn how we can connect to SQL Server via DAC and later on change the memory configuration.

First open command prompt and type the following command.

C:\>sqlcmd -S ServerName -U sa -P TypeYourPassword –A

Next, run the following command which will increase your memory to 3 GB of memory.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 3072;  
GO  
RECONFIGURE;  
GO

If your server has more memory, I suggest you set this value to a much higher number. Once it was changed to a higher value, the server was able to restart.

Do you ever face such issues, I would like to hear them in the comments of this blog.

If you have any questions, you can always reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)