Configure SQL Server 2005 Maximum Memory Usage

Have you ever found your PC running slowly after playing around with a particularly large SQL Database on you local machine? Well then, it’s possible that you have left the SQL Server ‘Maximum server memory’ setting at its default.

The default is 2,147,483,647 MB – Yes thats  bytes 2,251,799,812,636,672, or about 2 Petabytes.

SQL Server Configure Maximum Memory Usage

SQL Server Configure Maximum Memory Usage

I want a PC with that amount of memory!

To configure max memory usage for a particular instance of SQL Server 2005/2008 (and possibly later versions as and when they come :-)), simply adjust this setting in the above dialog, which can be located by right-clicking your database instance in SQL Management Studio, and selecting Properties

Alternatively, you might sneer at the novice user interface shortcut and decide you want to script it:

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'max server memory (MB)', N'300' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

The above code reconfigures the setting to 300 as shown in the second line.

Leave a Reply