Little SQL Server Tricks: Fix Strange Problems by Reducing the Available Memory

The installer of SQL Server has a little annoyance: By default, the available memory for your database is set to 2147483647 (or Int32.MaxValue) :

Notice the checkbox in the installer

If you do not notice the checkbox at the end and check it, you allow SQL Server to take every last byte of RAM. This greedy monster will go unnoticed for a long time. You may never experience any problem caused by this misconfiguration.

However, a more likely scenario is that at one day in the distant future (or tomorrow) strange little errors happen. One job in SQL Server Integration Services fails, then another one, and so forth. It may take you a long time to figure out the source of these problems – it sure took us a long time.

All those pesky little errors stopped immediately after we changed the available memory to a value 2 GB less than the amount of memory we assigned to the machine. You can do this in the server properties

Server properties are in the context menu on the server name

by the Memory settings:

The memory settings allow you to change the amount of RAM used

You need to restart the database server to get this change activated. Since we did this simple little change these strange errors stopped. Try it if your SQL Server suddenly runs into strange errors.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.