MT-Consulting.Net

a(1). Firstly, we need to use the steps below to identify what instance is using the most memory: In Task Manager, click Select Columns in the View menu, and make sure that PID (Process Identifier) is checked. Then find out the sqlservr.exe process that is using the most of the RAM and note its PID. Once you have the PID run the following from a command prompt:

tasklist /svc

This should give you the instance associated with the PID you identified in the previous step. And then you can configure a maximum amount of memory to the instance.

(2). Then, use max server memory to prevent SQL Server from using more that the specified amount of memory (we can also use min server memory to guarantee a minimum amount of memory to an instance of SQL Server). To do so, open a command prompt and run the following command:

osql -E -S <server name>\<instance name>

You will enter the osql command prompt. Run the commends below (replace <xxx> with the maximum and minimum amount of memory respectively):

1> EXEC sp_configure 'show advanced options', 1
2> reconfigure
3> go

1> EXEC sp_configure 'min server memory', <xxx>
2> reconfigure
3> go

1> EXEC sp_configure 'max server memory', <xxx>
2> reconfigure
3> go

After doing the above steps, you may run the commands to check the configured memory usage:

1> EXEC sp_configure
2> go