Summary

A default Microsoft SQL Server installation does not specify a memory limit. This can lead to situations where SQL Server will consume all available memory on the computer hosting SQL Server. You may wish to specify a maximum memory limit.

More Information

Use Enterprise Manager or SQL Server Management Studio to set a maximum server memory limit. You can also use the OSQL command line utility to modify this value.

Enterprise Manager

1. In Enterprise Manager, right-click on the desired SQL Server instance and click Properties.
2. In the properties dialog box, click the Memory tab.
3. Under the Maximum (MB) slider option, move the slider to the desired maximum value.
4. Click OK to save your changes.

Management Studio

1. In SQL Server Management Studio, connect to the desired SQL Server database engine, right-click the desired instance and click Properties.
2. In the Server Properties dialog box, select the Memory item from the list on the left.
3. In the Maximum server memory (in MB) option, specify the desired maximum value.
4. Click OK to save your changes.

OSQL

1. From the Start menu, select Run.
2. Type the following and press Enter:

   cmd

3. Type the following at the command prompt and press Enter:

   osql -E -S SQLInstance

   SQLInstance is the name assigned to the desired instance of SQL Server. This term should be replaced by the name of the computer hosting SQL Server. If you are using MSDE (i.e. Laserfiche Server (Team) or Standalone), then you may also need to append "\Laserfiche" (e.g. Computer\Laserfiche).

4. You should see a prompt that looks like this:

   1>

5. By default, the maximum memory setting may not be available for viewing and changing. To modify this behavior, type the following, pressing Enter after each line:

   USE master
   EXEC sp_configure 'show advanced options', 1
   RECONFIGURE WITH OVERRIDE
   GO

6. To set a maximum memory limit, type the following, pressing Enter after each line:

   USE master
   EXEC sp_configure 'max server memory (MB)', MaxServerMemory
   RECONFIGURE WITH OVERRIDE
   GO

   MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate.

7. To hide the maximum memory setting, type the following, pressing Enter after each line:

   USE master
   EXEC sp_configure 'show advanced options', 0
   RECONFIGURE WITH OVERRIDE
   GO

8. Type the following and press Enter to exit OSQL:

   exit