Category: SQL

SQL Server 2008 Maximum Server Memory

I ran into a scenario this week where a runaway query on a 1.4 million record database caused SQL Server’s memory usage to balloon from 150MBs to 4GBs in just a few minutes.  I was surprised that SQL Server let the process consume that much of the system resources. So after a couple minutes digging I discovered that the default maximum server memory setting for SQL Server 2008 is 2147483647 MBs, or a ridiculous 2048 Terabytes. You’ve gotta be kidding me!

Now granted it’s rare to have a runaway query like that, but I don’t understand why the max memory setting is left wide open by default. Why doesn’t the installer automatically figure out the default setting based on available RAM, and adjust it accordingly based on some algorithm? Maybe there’s a good reason for this, but on the surface it sounds like a serious oversight.

Solution

So, for the purpose of our needs I cranked down the maximum server memory setting to 50% of existing RAM using SQL Server Management Studio. Your needs may be different and I recommend you read Microsoft’s documentation carefully, the link is included below. It’s been a long time since I’ve had a corrupted database, and running out of system memory seemed like an almost guaranteed way to experience that.

Reference:

SQL Server Memory Options

Migrating from SQL Server 2005 to SQL Server 2008

Basically, I needed to migrate a fairly simple database from SQL Server 2005 to SQL Server 2008 for a project I’m working on. The database has six tables, dozens of columns, properties and unique keys. There was also a stored procedure and a trigger just to spice things up.

I was quite happy with how easy and fast the migration went. I’ve had seemingly simple database tasks go sideways before and take hours to resolve. From beginning to end, this whole operation took about five minutes.

Here are the steps I used, although yours may vary slightly.

  1. Launch SQL Server Management Studio on your SQL Server 2005 machine
  2. Right click on the actual database then click Tasks > Detach. This step insures there are no active processes accessing the database.
  3. Since my SQL Server 2008 database was locked down, I copied the .mdf and .log files from the SQL Server 2005 machine over to the corresponding directory on the SQL Server 2008 machine via Remote Desktop. SQL Server does have an option to copy from one database directly into another. However, that involves opening ports in firewalls to allow access between the server, and I didn’t have that luxury for security reasons. 

    4. Note 1:  Make double sure you don’t overwrite an active database of the same name on the destination server!

    Note 2:  the default installation directory for these files on SQL Server 2008 is here: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

  4. Launch SQL Server Management Studio on your SQL Server 2008 machine.
  5. Right click on “Databases” then select “Attach”.
  6. This will launch the Attach Databases window. Select “Add” and navigate to the correct .mdf file that you just copied over.
  7. Follow any additional prompts.  I didn’t have any full-text catalogs so I just clicked “OK” when that pop-up window appeared.  For me , SQL Server 2008 automatically converted the old SQL Server 2005 database into SQL Server 2008.

If you follow these steps, and if there were no errors, you are almost done. Before you pop champagne and celebrate make sure you can access the database from SQL Server Management Studio. Open the tables and run a quick test query just to make sure. If you have a more complex database you might want to run multiple test queries, and run your application, just to make sure everything moved over properly.  If you have a migration story please share it here.